how to do data filter using created_at

stackover flow 0
Viewed 45

MyCode

$datas = DB::table('patrol_gate_surveillance_transactions as a')

    ->leftJoin('client_locations as b','a.client_location_id', 'b.id')
    ->leftJoin('clients as c', 'b.client_id', 'c.id')
    ->select('a.client_location_id as location_id', 'b.name as location_name', 'c.name as client_name',
        DB::raw('SUM(CASE WHEN a.type = 1 THEN employee_qty ELSE 0 END) as e_qty_in'),
        DB::raw('SUM(CASE WHEN a.type = 2 THEN employee_qty ELSE 0 END) as e_qty_out')
    )
    ->groupBy('location_id', 'location_name', 'client_name')
    ->get();

#MyProblem

I can't add created_at to the SELECT because it will interfere with the GROUPING I've created.

myTable

enter image description here

mysql php laravel
1 Answers

You won't be able to group by created_at as they are all different. It's not 100% clear what you're trying to do, but you can group by a part of the date if that makes sense.

select year(created_at), month(created_at), (other cols to group)
from patrol_gate_surveillance_transactions
where ...
group by 1, 2
order by 1 desc, 2 desc
Drewster0
Related