MySQL - Sort results in descending order of overall count

Question

I have a table as below

year -------- org ------- name ---------- category -------- points
2005 -------- ABC ------- N1 ---------- CAT1 -------- 10
2006 -------- DEF ------- N2 ---------- CAT2 -------- 5
etc

Primary key in this table is (year, org, name)

I need an output as below

org ------- category ------ points (sorted in descending order of overall points of org)
DEF ------- CAT1 ------ 1000
DEF ------- CAT2 ------ 5000
DEF ------- CAT3 ------ 2000
ABC ------- CAT1 ------ 6000
ABC ------- CAT2 ------ 100
ABC ------- CAT3 ------ 50

DEF score is 8000 which is higher than the score of ABC which is 6150. So appears at the top of the expected output

I wrote a select statement as below

select org, cat, count(cat) from table where year=2006 group by org, cat order by org

I get the result ordered by org but I am unable to get the output sorted in descending order of overall count of points of every type of org

Any help is much appreciated. Thanks - Praveen

The Scrum Meister The Scrum Meister · Accepted Answer · 2011-02-17T05:30:41

Use a JOIN to group the org alone, and get the SUM of all the counts for each group:

SELECT t.org, t.cat, count(t.cat)
FROM table t JOIN (
    SELECT org, count(cat) SumCount
    FROM table
    WHERE year=2006 
    GROUP BY org
) tg ON t.org = tg.org
WHERE t.year = 2006 
GROUP BY t.org, t.cat
ORDER BY MAX(rs.SumCount), t.org DESC

MySQL - Sort results in descending order of overall count

2 Answers