如何在 MySQL 中按 SUM() 排序？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1309841/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to ORDER BY a SUM() in MySQL?
提问by lovespring
I have a table: "ID name c_counts f_counts "
我有一张表：“ID 名称 c_counts f_counts”
and I want to order all the record by sum(c_counts+f_counts)but this doesn't work:
我想订购所有记录，sum(c_counts+f_counts)但这不起作用：
SELECT * FROM table ORDER BY sum(c_counts+f_counts) LIMIT 20;
SELECT * FROM table ORDER BY sum(c_counts+f_counts) LIMIT 20;
回答by gahooa
Don'y forget that if you are mixing grouped (ie. SUM) fields and non-grouped fields, you need to GROUP BY one of the non-grouped fields.
不要忘记，如果您混合分组（即 SUM）字段和非分组字段，则需要对非分组字段之一进行 GROUP BY。
Try this:
尝试这个：
SELECT SUM(something) AS fieldname
FROM tablename
ORDER BY fieldname
OR this:
或这个：
SELECT Field1, SUM(something) AS Field2
FROM tablename
GROUP BY Field1
ORDER BY Field2
And you can always do a derived query like this:
你总是可以像这样进行派生查询：
SELECT
f1, f2
FROM
(
SELECT SUM(x+y) as f1, foo as F2
FROM tablename
GROUP BY f2
) as table1
ORDER BY
f1
Many possibilities!
多种可能性！
回答by Virat Kadaru
This is how you do it
这就是你的方法
SELECT ID,NAME, (C_COUNTS+F_COUNTS) AS SUM_COUNTS
FROM TABLE
ORDER BY SUM_COUNTS LIMIT 20
The SUM function will add up all rows, so the order byclause is useless, instead you will have to use the group byclause.
SUM 函数会将所有行相加，因此该order by子句无用，您必须使用该group by子句。
回答by DarkAjax
You could try this:
你可以试试这个：
SELECT *
FROM table
ORDER BY (c_counts+f_counts)
LIMIT 20
回答by jrista
Without a GROUP BY clause, any summation will roll all rows up into a single row, so your query will indeed not work. If you grouped by, say, name, and ordered by sum(c_counts+f_counts), then you might get some useful results. But you would have to group by something.
如果没有 GROUP BY 子句，任何求和都会将所有行汇总为一行，因此您的查询确实不起作用。如果您按名称分组并按 sum(c_counts+f_counts) 排序，那么您可能会得到一些有用的结果。但是你必须按某些东西分组。
回答by user158017
The problem I see here is that "sum" is an aggregate function.
我在这里看到的问题是“sum”是一个聚合函数。
first, you need to fix the query itself.
首先，您需要修复查询本身。
Select sum(c_counts + f_counts) total, [column to group sums by]
from table
group by [column to group sums by]
then, you can sort it:
然后，您可以对其进行排序：
Select *
from (query above) a
order by total
EDIT: But see post by Virat. Perhaps what you want is not the sum of your total fields over a group, but just the sum of those fields for each record. In that case, Virat has the right solution.
编辑：但请参阅 Virat 的帖子。也许您想要的不是组上的总字段总和，而只是每条记录的这些字段的总和。在这种情况下，Virat 有正确的解决方案。
