Laravel collection sum of columns group by month

Question

I'm working on Laravel project where I need to fetch some data, sum the columns and group the results in M-y (Jan-19) format to show in a monthly bar graph chart.

I was able to successfully get the data sum'ed and grouped but the problem is, for the months without any records, I still want to show up in the graph with a total equals to 0.

This is my current approach:

$data = $this->user->income()
              ->whereBetween('game_date', ['2019-01-01', '2019-04-30'])
              ->selectRaw('sum(total_score) as score,
                           sum(total_stars) as stars,
                           MONTH(game_date) as month,
                           DATE_FORMAT(game_date,"%b") as month_name
                          ')
              ->groupBy('month', 'month_name')
              ->get();

This gives me the following result (missing months without any records):

[    
    {
        "score": "707",
        "stars": "64",
        "month": 1,
        "month_name": "Jan"
    },
    {
        "score": "200",
        "stars": "29",
        "month": 3,
        "month_name": "Mar"
    }
]

And the expected result is including missing months to making visually clear chart as:

[
    {
        "score": "707",
        "stars": "64",
        "month": 1,
        "month_name": "Jan"
    },
    {
        "score": "0",
        "stars": "0",
        "month": 2,
        "month_name": "Feb"
    },
    {
        "score": "200",
        "stars": "29",
        "month": 3,
        "month_name": "Mar"
    },
]

Please note that the ->whereBetween('game_date', []) will aways have dates from start of month and end of month (covering full monthly records).

score 0 · Answer 1 · answered Jul 05 '19 at 15:14

0

you have to use COALESCE() function. It's because at the month of Feb you get null at sum(total_score) as score instead of 0. look at

here and here

hop it help

answered Jul 05 '19 at 15:14

kfir88

380
2
16

It returns the same result. I think because of there is no date record for the Feb month so it can't group by the month Feb. – sohal07 Jul 06 '19 at 01:11
Any other solution to tackle this? – sohal07 Jul 06 '19 at 01:11

Laravel collection sum of columns group by month

1 Answers1