x13 x13 - 7 months ago 9
SQL Question

mysql query (multiple counts)

With this query:

SELECT abc, def, COUNT(*) as c
FROM xpto
GROUP BY abc, def
ORDER BY abc, c DESC


I have this result:

xpto

abc | def | c

x | c_1 | 8
...
y | a_2 | 4
y | a_1 | 2
y | a_3 | 1

z | b_2 | 7
z | b_1 | 3
...


I wish to have this result (ordered by number of entries for each abc and field c):

y | a_2
y | a_1
y | a_3

z | b_2
z | b_1

x | c_1


Can a SQL guru help me with this task? I've seen this example SQL Help: Counting Rows in a Single Query With a Nested SELECT, is this a good solution (nested select), or there is no other way to do?

Thanks in advance

Answer

Check this query

SELECT abc, def, COUNT(*) as c,
  (SELECT COUNT(1) FROM (
        SELECT abc, def, COUNT(*) as c
        FROM xpto A
        GROUP BY abc, def
    ) B
  GROUP BY ABC
  HAVING C.abc=B.abc
  ) ic
FROM xpto C
GROUP BY abc, def
ORDER BY ic DESC, c DESC

This may not be the best way to achieve this, but it works (Check this SQL Fiddle)