CODEWITHSUNDEEP
sqlmysqlsql-order-by
lovespring
lovespring

Reputation: 19569

How to ORDER BY a SUM() in MySQL?

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;

Upvotes: 49

Views: 172830

Answers (5)

DarkAjax
DarkAjax

Reputation: 16223

You could try this:

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

Upvotes: 7

user158017
user158017

Reputation: 2991

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.

Upvotes: 0

jrista
jrista

Reputation: 32960

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.

Upvotes: 0

Virat Kadaru
Virat Kadaru

Reputation: 2256

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.

Upvotes: 9

gahooa
gahooa

Reputation: 137332

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!

Upvotes: 92

Related Questions