John Davidson

Message:

This query works with group by

$data = DB::select(DB::raw("SELECT  SUM(pending) as total ,  datediff(NOW(), created_at) 

as maturity FROM `admin_case_info` WHERE deleted_at is NULL group by maturity "));

But when I joined 2 tables.

$data = DB::select( DB::raw("SELECT admin_case_info.case_type, admin_case_info.case_status, SUM(admin_case_info.pending) as total, 

datediff(NOW(), admin_case_info.created_at) as maturity FROM admin_case_info 

JOIN users_mws on users_mws.id = admin_case_info.users_mws_id

JOIN users on users.id = users_mws.user_id

 WHERE users.status NOT IN ('Dumped', 'Dumped2')

AND admin_case_info.deleted_at IS NULL group by maturity") );

It does not work. I need to modify the config/database strict to false in order it to work and I dont want to do it. What should I do? Thanks

---

My Answer:
SELECT id, name, COUNT(*) as total
FROM users
GROUP BY id, name
ORDER BY total DESC
"));

However, if you are using Laravel's query builder, you can achieve the same result using the groupBy() method like this:

$data = DB::table('users')
->select('id', 'name', DB::raw('COUNT(*) as total'))
->groupBy('id', 'name')
->orderBy('total', 'desc')
->get();