如何在MySQL中按SUM()排序?

I have a table: "ID name c_counts f_counts "

我有一个表:“ID name c_count f_count”

and I want to order all the record by sum(c_counts+f_counts) but this doesn't work:

我想对所有记录进行求和排序(c_counts+f_counts)，但这行不通:

SELECT * FROM table ORDER BY sum(c_counts+f_counts) LIMIT 20;

按总和(c_counts+f_counts)从表订单中选择*，限制为20;

5 个解决方案

#1

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)字段和非分组字段，需要由一个非分组字段分组。

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!

许多可能性!

#2

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 by clause is useless, instead you will have to use the group by clause.

SUM函数将把所有的行相加，因此order by子句是无用的，相反，您必须使用group by子句。

#3

You could try this:

你可以试试这个:

SELECT * 
FROM table 
ORDER BY (c_counts+f_counts) 
LIMIT 20

#4

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)排序，那么您可能会得到一些有用的结果。但是你必须把它们分组。

#5

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的post。也许您想要的不是对一个组的所有字段的总和，而是每个记录的这些字段的总和。在这种情况下，Virat有正确的解决方案。

#1