A raw Laravel query that takes into account monthly results and aggregates

Question

I have the following request

$this->builder = (DB::table('sales')
        ->select(DB::raw('DATE_FORMAT(sale_date, "%M") as sale_date'), DB::raw('count(*) as sales'))
        ->where('sale_date', '>', $lookback->toDateTimeString())
        ->orderBy('sale_date', 'asc')
        ->groupBy('sale_date'));

Despite specifying a month %Mto group the results, it simply counts each date and gives the label as a month, so something like this:

sale_date , sales
 August   ,  1
 August   ,  3
 August   ,  2

Instead of what I expect:

sale_date , sales
 August   ,  6

What am I doing wrong here, do I have the ability to simply summarize those that are in javascript when I get the results from the API, however I don’t think it should be necessary correctly?

Answer 1

DATE_FORMAT(sale_date, "%M"). sale_date, ,

$this->builder = (DB::table('sales')
        ->select(DB::raw('DATE_FORMAT(sale_date, "%M") as sale_month'), DB::raw('count(*) as sales'))
        ->where('sale_date', '>', $lookback->toDateTimeString())
        ->orderBy('sale_date', 'asc')
        ->groupBy('sale_month'));

, DB::raw() groupBy()

->groupBy(DB::raw('DATE_FORMAT(sale_date, "%M")'))

Answer 2

:

  $this->builder = (DB::table('sales')
    ->select(DB::raw('DATE_FORMAT(sale_date, "%M") as sale_month'),
     DB::raw('sum(count) as sales'))
    ->where('sale_date', '>', $lookback->toDateTimeString())
    ->orderBy('sale_date', 'asc')
    ->groupBy('sale_month'));