Organizational Research By

Surprising Reserch Topic

how to order by a sum in mysql


how to order by a sum in mysql  using -'sql,mysql,sql-order-by'

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;
    

asked Sep 24, 2015 by r3tt
0 votes
5 views



Related Hot Questions

3 Answers

0 votes

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!

answered Sep 24, 2015 by amit.gupta
0 votes

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.

answered Sep 24, 2015 by ukohale
0 votes

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.

answered Sep 24, 2015 by ajit.chavhan

...