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
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community