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  LIMIT 20

User · Answer

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

User · Answer

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

User · Answer

You could try this   SELECT    FROM table  ORDER BY  c counts f counts   LIMIT 20

User · Answer

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

User · Answer

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

[sql] How to ORDER BY a SUM() in MySQL?

Examples related to sql

Examples related to mysql

Examples related to sql-order-by