nickdavies07
8 months ago

SQL Query Builder - Grouping and Ordering

Posted 8 months ago by nickdavies07

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!

Please sign in or create an account to participate in this conversation.