CODEWITHSUNDEEP
phpmysqllaraveleloquent
Ahmed Abdulrahman
Ahmed Abdulrahman

Reputation: 469

MySQL Syntax error: ORDER BY clause is not in GROUP BY

I have a query where I want to order by shipping_date but I get this error:

QueryException in Connection.php line 770:
    SQLSTATE[42000]: Syntax error or access violation: 1055 Expression 1 of 
    ORDER BY clause is not in GROUP BY clause and contains nonaggregated 
    column 'device.devices.shipping_date' which is not functionally 
    dependent on columns in GROUP BY clause; this is incompatible with 
    sql_mode=only_full_group_by (SQL: select count(*) as device, 
    DATE_FORMAT(shipping_date,"%M %Y") as year from `devices` where 
    `shipping_date` is not null and `internal_use` = 0 group by `year` order by `shipping_date` asc)

My code:

$sold_devices = Device::selectRaw('count(*) as device, DATE_FORMAT(shipping_date,"%M %Y") as year')
                 ->whereNotNull('shipping_date')
                 ->where('internal_use', '=', 0)
                 ->groupBy('year')
                 ->orderBy('shipping_date', 'asc')
                 ->pluck('device', 'year');

any help?

Thanks

Upvotes: 1

Views: 744

Answers (2)

Stefano Zanini
Stefano Zanini

Reputation: 5916

You have to specify the exact select column in your group by: in your case

->groupBy('DATE_FORMAT(shipping_date,"%M %Y")')
->orderBy('DATE_FORMAT(shipping_date,"%M %Y")', 'asc')

Generally speaking, you can't use column aliases in where, group by and order by clauses.

Edit

To change the ordering criteria you can use the numeric value of the month instead of its name:

->groupBy('DATE_FORMAT(shipping_date,"%M %Y")')
->orderBy('DATE_FORMAT(shipping_date,"%Y%c")', 'asc')

Upvotes: 1

Saravanan Nandhan
Saravanan Nandhan

Reputation: 612

let's try this.

it will work...

 ->groupBy(DB::raw('DATE_FORMAT(shipping_date,"%M %Y")'))
   ->orderBy(DB::raw('DATE_FORMAT(shipping_date,"%M %Y"))','asc')

Upvotes: 0

Related Questions