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();
Rate this post
3 of 5 based on 6814 votesComments