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:
SELECT *, COUNT(type) AS typecount
GROUP BY type
ORDER BY typecount DESC, type ASC
id type count
2 b 3
1 c 2
4 a 1
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