SQL Query Builder - Grouping and Ordering

Think this is more an SQL query but here goes...

I need to get a set of results from one of my tables to output to a chart. The chart needs to show "the number of concerns for each month, for this academic year (1st Sept 2018 to 31st August 2018)".

The returned result should look something like:

| month     | total |
|-----------|-------|
| September | 15    |
| October   | 23    |
| December  | 24    |
| January   | 438   |

The problem comes when I try and group the month together; i get an incorrect count value.

The query I currently have is:-

    // Returns 2018-09-01 00:00:00
        $start = Carbon::createMidnightDate(Carbon::now()->subMonths(8)->year, 9, 1);
    // Gets current date
        $end = Carbon::now();

        $concerns = DB::table('concerns')
            ->select(DB::raw('MONTHNAME(created_at) as month, count(*) as total, created_at'))
            ->whereBetween('created_at', [$start, $end])
            ->groupBy('month')
            ->groupBy('created_at')
            ->orderBy('created_at', 'asc')
            ->get()->mapWithKeys(function ($item) {
                return [$item->month => $item->total];
          });

However this returns me:-

| month     | total |
|-----------|-------|
| September | 1     |
| October   | 1     |
| December  | 1     |
| January   | 1     |

It's clearly not adding all of the individual values together because of the grouping of the created_at date, however excluding it prevents me from ordering it due to the MySQL only_full_group_by mode.

Any advice would be hugely appreciated!

MartinP

7 years ago

Level 2

Can you not leave the created_at field out of the query alltogether and instead, orderby('month')?

From a pure SQL side of things, you're trying to group things together but also showing the detail (i.e. group by month, but select individual dates too) which is why you're getting those results

// Returns 2018-09-01 00:00:00
        $start = Carbon::createMidnightDate(Carbon::now()->subMonths(8)->year, 9, 1);
    // Gets current date
        $end = Carbon::now();

        $users = DB::table('concerns')
            ->select(DB::raw('MONTHNAME(created_at) as month, count(*) as total'))
            ->whereBetween('created_at', [$start, $end])
            ->groupBy('month')
            ->orderBy('month'_at', 'asc')
            ->get()->mapWithKeys(function ($item) {
                return [$item->month => $item->total];
          });

josefelipetto

7 years ago

Level 3

Since you have to use ->groupBy('created_at'), I think you can place it after the ->get(). In that way you are ordering the result collection and not query itself.

nickdavies07

7 years ago

Best Answer

Level 8

Thanks for your answers.

I had a response that worked on Stackoverflow, so I'll leave that here in case anyone needs it in future!

$concerns = DB::table('concerns')
    ->select(DB::raw('MONTHNAME(created_at) AS month, YEAR(created_at) AS year, count(*) as total'))
    ->whereBetween('created_at', [$start, $end])
    ->groupBy('year', 'month')
    ->orderBy('year', 'asc')
    ->orderByRaw("MONTH(STR_TO_DATE(CONCAT('1 ', month, ' ', year), '%e %M %y')) asc")

1 like

Please or to participate in this conversation.