Eloquent MySql equivalent of this Sqlite Query

Hi,

In the chart.js series, @jeffery_way used sqlite and wrote this

Preformance::thisYear() ->selectRaw('strftime("%m", created_at) as month, sum(revenue) as revenue') ->groupBy('month') ->pluck('revenue'); https://laracasts.com/series/charting-and-you/episodes/6 to get the sum of transactions within the last year, grouped by months.

I am trying to do something similar in a real life project, what is the mysql equivalent of this..?

tykus

10 years ago

Best Answer

Level 104

Performance::thisYear()
          ->selectRaw('DATE_FORMAT(created_at, "%m") as month, sum(amount) as revenue')
          ->groupBy('month')
          ->pluck('revenue');

Edit lowercase 'm' for month as padded digits

1 like

Lordwhizy

10 years ago

Level 13

Hi @tykus_ikus Thanks for your answer, work perfectly.

I kinda need more help regarding the query.

when i do

    Performance::thisYear()
          ->selectRaw('DATE_FORMAT(created_at, "%m") as month, sum(amount) as revenue')
          ->groupBy('month')
          ->pluck('revenue',  'revenue');

I get the a key value pair array with the month as key and the sum of amount in that month as value. Problem is the result does not seem to be in order. July last year which is "07" => "5000", appears after june this year "06" => "6000"

        // this is what i get now
          [ "03" => 1000.0,       // march 2016
                "06" => 1020.0,       // june 2016
                    "07" => 20.0 ,           // july 2015
                    "09" => 10.0 ]          // september 2015
            
               // I will like to have it like this
         [ "07" => 10.0,               // july 2015
            "08" => 0,                    // august 2015
                    "09" => 10.0              //september 2015
                    "10" => 0,                   // october 2015
                    "11" => 0,                   // november 2015
                    .....
                    .....
                "03" => 1000.0,       // march 2016
                    "04" => 0,           // april 2016
                    "05" => 0,          // may 2016
                    "06" => 1020.0]      // june 2016

How do I make empty months return 0 and get the result arranged by month from the last 12 month up.

I hope you understand my weak explanation.

Thanks

Please or to participate in this conversation.