Laravel Query Builder with sum

Question

below is the query , and i am having this error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

 $games = DB::table('games') ->select('start','dayofweek','name','ins_id','location_id', DB::raw('SUM(reservations) as reservations'), DB::raw('SUM(reservationsmax) as reservationsmax'), DB::raw('SUM(entries) as entries'), DB::raw('SUM(entriesmax) as entriesmax'), DB::raw('(SUM(entries) / SUM(reservationsmax) * 100 as percentage')) ->where('club_id', $club_id) ->whereBetween('start', [$from, $to]) ->where('hide_from_customers', 0) ->where('external_id', 0) ->orderBy('name') ->groupBy('start') ->groupBy('dayofweek') ->groupBy('name') ->get();

Muhabutti · Accepted Answer · 2017-05-12 09:51:14Z

i have found the answer, i thought it will would help some one else as well.

$games = \App\Models\Games::select(DB::raw('start,dayofweek,name,instructor_id,location_id,SUM(reservations) as reservations,SUM(reservationsmax) as reservationsmax,SUM(entries) as entries,SUM(entriesmax) as entriesmax,(SUM(entries) / SUM(reservationsmax)) * 100 as percentage')) ->where('club_id', $club_id) ->whereBetween('start', [$from, $to]) ->where('hide_from_customers', 0) ->where('external_id', 0) ->orderBy('name') ->groupBy('start') ->groupBy('dayofweek') ->groupBy('name') ->get();

MatHatrik · Accepted Answer · 2017-05-12 09:17:43Z

 Something like this .... $games = DB::table('games') ->select(DB::raw(' SUM(reservations) as reservations, SUM(reservationsmax) as reservationsmax, SUM(reservationsmax) as reservationsmax, SUM(entries) as entries, SUM(entriesmax) as entriesmax, SUM(entries) / SUM(reservationsmax) * 100 as percentage')) ->where('club_id', $club_id) ->whereBetween('start', [$from, $to]) ->where('hide_from_customers', 0) ->where('external_id', 0) ->orderBy('name') ->groupBy('start') ->groupBy('dayofweek') ->groupBy('name') ->get();

Ii have tried with single group by ->groupBy('start','dayofweek','name'), still hacing same error
try running the query without any of the where,orderBy and groupBy clauses. Then add each one in one at a time.As its hard to find the issue when i dont have the vairable values for $from, $to etc ....
club_id = '25', hide_from_customers = 0, to = '2017-05-11', from = '2017-05-01'
Thanks for your help, i have fixed the query builder syntax.

Collectives™ on Stack Overflow

Laravel Query Builder with sum

2 Answers 2

Comments

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Related