Support the ongoing development of Laravel.io →
Database Eloquent

Dear All, I have query builder :

 DB::select("select `id`, `user_name`, `user_email`, (select sum(user_score) from users_data group by user_id having user_id = users.id) as total_score, (row_number() over (order by total_score desc)) as ranks from `users` order by `total_score` desc");

trying use query builder

     $data = DB::table("users")->select("id", "user_name", "user_email",
                DB::raw("(select sum(user_score) from users_data group by user_id having user_id = users.id) as total_score"),
                DB::raw("row_number() over (order by total_score desc) as ranks"))
            ->orderBy("total_score", "desc")
            ->get();

all failed with message was :

SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

but while I've run sql script which shown on logs, using phpmyadmin , result was correctly.

select `id`, `user_name`, `user_email`, `user_gids`, (select sum(user_score) from users_data group by user_id having user_id = users.id) as total_score, (row_number() over (order by total_score desc)) as ranks from `users` order by `total_score` desc

May i know what I've missing in here ? Thank you a lot

Best Regards,

qlixes

Last updated 3 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

qlixes qlixes Joined 11 Sep 2019

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2025 Laravel.io - All rights reserved.