Larave Join table and query

I have 3 table. Users, Accounts and Booking. I have try three table join and year and month wise query. 3 Table diagram

But query booking calculation is show wrong. Its show double quantity.

$january = DB::table('users')
        ->join('bookings', 'users.id', '=', 'bookings.user_id')
        ->join('accounts', 'users.id', '=', 'accounts.user_id')
        ->orderBy('users.room_id','asc')
        ->groupBy('users.id')
        ->whereYear('bookings.bookingdate','=', '2016')
        ->whereMonth('bookings.bookingdate','=','01')
        ->whereYear('accounts.accountdate','=', '2016')
        ->whereMonth('accounts.accountdate','=','01')
        ->select('users.*',
            DB::raw("COUNT(case when bookings.breakfast='on' then 1 else null end) AS t_breakfast"),
            DB::raw("COUNT(case when bookings.lunch='on' then 1 else null end) AS t_lunch"),
            DB::raw("COUNT(case when bookings.dinner='on' then 1 else null end) AS t_dinner"),
            DB::raw("SUM(accounts.amount) AS t_amount")
        )
        ->get();

    dd($january);

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/1PN7z9X
via IFTTT