How to ORDER BY a SUM() in MySQL?

Question

I have a table: "ID name c_counts f_counts "

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

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

Answer 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.

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!

Answer 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.

Answer 3

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.

Answer 4

The problem I see here is that "sum" is an aggregate function.

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.