Getting the employee of the month from database using eloquent

I have this table ;

And I need to get employee_id and total values for current and previous months but only the employees who has max value of total per each month, so 2 records in total for best selling employee of this month and previous one.

I'm having trouble with groupby statements (I guess), here's the code I tried and the error message I get.

$employee_sales = EmployeeMonthlySales::query()
    ->whereIn(
        DB::raw("CONCAT(month,'-',year)"),
        [$datetime_now->format('n-Y'), $datetime_prev->format('n-Y')])
    ->get(['employee_id', DB::raw('MAX(total) as max_total')])
    ->groupBy('month')->dd();

ERROR : SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'storefinal.employee_monthly_sales.employee_id'; this is incompatible with sql_mode=only_full_group_by

psrz

3 years ago

Best Answer

Level 10

If I understand this correctly you want the top seller for each month, current and previous one, right ?

If that's the case then first you need to grab the max totals for each month

    $maxSales = EmployeeMonthlySales::query()
        ->select(['year','month', DB::raw('max(total) as total')])
        ->whereIn(
            DB::raw("CONCAT(month,'-',year)"),
            [$datetime_now->format('n-Y'), $datetime_prev->format('n-Y')]
        )
        ->groupBy(['year', 'month'])
    ;

So then you use that as derived table to join with monthly sales and get the employee_id

    $topSellers = EmployeeMonthlySales::query()
        ->select(['employee_id','max_sales.*'])
        ->joinSub($maxSales, 'max_sales', function($join) {
            $join
                ->on('monthly_sales.year','=','max_sales.year')
                ->on('monthly_sales.month', '=', 'max_sales.month')
                ->on('monthly_sales.total', '=', 'max_sales.total');
        })
        ->get()
    ;

That should work.

BTW, this doesn't guarantee you'll get only two records because there is the small chance of two -or more- employees being at the top of a month

1 like

motinska94

3 years ago

Level 3

@psrz Thanks for the answer. It almost works, but can you explain where do you get monthly_sales and max_sales?

I'm getting this error : Column not found: 1054 Unknown column 'monthly_sales.year' in 'on clause'

psrz

3 years ago

Level 10

@motinska94

I don't know the name of the table of the table you posted. Figured it could be monthly_sales

max_sales is the alias for the derived table. That can be anything you want.

https://laravel.com/docs/10.x/queries#subquery-joins

1 like

motinska94

3 years ago

Level 3

@psrz Oh I got it. I changed it to my table name (employee_monthly_sales) and it worked beautifully! Thanks a lot!

Please or to participate in this conversation.