How to use sum function to filter window functions in MYSQL-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

How to use sum function to filter window functions in MYSQL

P粉186904731 2023-08-30 22:03:52

519

How can I write this query to filter out only results with a sum greater than 50? I can't get it to work using having or sum, but I'm sure there is some way.

select name, sum(score) from submissions inner join hacker on submissions.hacker_id = hacker.hacker_id group by submissions.hacker_id order by sum(score) desc having sum(score) > 50

Here is an example with a table (there is nothing special about the table, this query runs without the last row, but returns everyone's score and name): http://sqlfiddle.com/# !9/7a660d/16

P粉186904731

reply all (2)

P粉8771147982023-08-31 15:51:51 2 floor

Order by should be in last

select name, sum(score) from submissions inner join hacker on submissions.hacker_id = hacker.hacker_id group by submissions.hacker_id having sum(score) > 50 order by sum(score) desc

Sort according to what should be at the end

选择 名称, 总分 从 提交 内连接 黑客 on 提交.黑客编号 = 黑客.黑客编号 分组 按 提交.黑客编号 筛选 总分 > 50 排序 按 总分 降序

Like+0

Add Reply

P粉9094764572023-08-31 00:57:15 1 floor

Your order by should be after your having. Right now:

select name, sum(score) from submissions inner join hacker on submissions.hacker_id = hacker.hacker_id group by submissions.hacker_id having sum(score) > 50 order by sum(score) desc

Like+0

Add Reply