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;
This question is related to
sql
mysql
sql-order-by
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.
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.
You could try this:
SELECT *
FROM table
ORDER BY (c_counts+f_counts)
LIMIT 20
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.
Source: Stackoverflow.com