CODEWITHSUNDEEP
phpmysqllaraveleloquent
GalahadXVI
GalahadXVI

Reputation: 839

Group by month and order the dates on Laravel Eloquent

I have the following query 

$months = \App\DATA::select(
    DB::raw('sum(order_value) as `sums`'),
    DB::raw("DATE_FORMAT(created_at,'%M %Y') as months")
)
    ->where("created_at", ">", \Carbon\Carbon::now()->subMonths(6))
    ->groupBy('months')
    ->get();

that makes a sum of the data for every month in the past 6 months. The problem is that it is outputting the dates in alphabetical order. 

August 2019
July 2019
June 2019
May 2019
November 2019
October 2019
September 2019

I cannot order it with a orderBy:

->orderBy('created_at')

due to the following problem

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

How can I order it by date?

Upvotes: 4

Views: 5662

Answers (2)

Dilip Hirapara
Dilip Hirapara

Reputation: 15296

Try with max function and ->orderBy('createdAt', 'desc').

$months = \App\DATA::select(
       DB::raw('sum(order_value) as `sums`'),
       DB::raw("DATE_FORMAT(created_at,'%M %Y') as months"),
       DB::raw('max(created_at) as createdAt')
)
      ->where("created_at", ">", \Carbon\Carbon::now()->subMonths(6))
      ->orderBy('createdAt', 'desc')
      ->groupBy('months')
      ->get();

Upvotes: 9

Roman Meyer
Roman Meyer

Reputation: 2872

Just order your data by created_at:

$months = \App\DATA::select(
    DB::raw('ANY_VALUE(created_at) AS created_at')
    DB::raw('sum(order_value) as `sums`'),
    DB::raw("DATE_FORMAT(created_at,'%M %Y') as months")
)
    ->where("created_at", ">", \Carbon\Carbon::now()->subMonths(6))
    ->orderBy('created_at')
    ->groupBy('months')
    ->get();

Upvotes: 0

Related Questions