SQL Sort by group size

Question

I want to make a select query which groups rows based on a given column and then sorts by size of such groups.

Let's say we have this sample data:

id type
1   c
2   b
3   b
4   a
5   c
6   b

I want to obtain the following by grouping and sorting the column 'type' in a descending way:

id type
2   b
3   b
6   b
1   c
5   c
4   a

As of now I am only able to get the count of each group but that is not exactly what I need:

SELECT *, COUNT(type) AS typecount
FROM sampletable
GROUP BY type
ORDER BY typecount DESC, type ASC

id type count
2   b    3
1   c    2
4   a    1

Can anybody please give me a hand with this query?

Edit: Made 'b' the biggest group to avoid coming to the same solution by using only SORT BY

Answer 1

You can't use a column alias in your GROUP BY; just repeat the expression:

SELECT type, COUNT(type) AS count 
FROM sampletable 
GROUP BY type 
ORDER BY COUNT(*) DESC, type ASC

Note that I changed the SELECT clause - you can't use * in your SELECT either since expressions in the SELECT need to either be in the GROUP BY clause or an aggregation.

Answer 2

It may not be the best way, but it will give you what you want.

You work out the totals for each group and then join that "virtual" table to your original table by the determined counts.

SELECT *
FROM sampletable s1
INNER JOIN (SELECT count(type) AS iCount,type
        FROM sampletable
        GROUP BY type) s2 ON s2.type = s1.type
ORDER BY s2.iCount DESC, s1.type ASC

http://sqlfiddle.com/#!9/f6b0c4/6/0