How to ORDER BY a SUM() in MySQL?

mysql, sql, sql-order-by

Solution

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!

Problem

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) LIMIT 20;`

Original source