Multiple COUNT fields, GROUP BY I'm trying to group two columns in my MySQL database and show counts for them respectively (as separate columns). Is there a way of doing this or do I have to join two sub-queries?

I'm aware of WITH ROLLUP but can't produce the right query.

This gives the right result:

SELECT A.mid, B.pid, A.count AS mid_count, B.count as pid_count
  FROM (Select mid , COUNT(*) AS count FROM test GROUP BY mid) AS A,
  (Select mid, pid, COUNT(*) AS count FROM test GROUP BY mid, pid) AS B 
  WHERE A.mid = B.mid ORDER BY mid_count DESC


This is the expected output

+-----+-----+-----------+-----------+
| mid | pid | mid_count | pid_count |
+-----+-----+-----------+-----------+
|   3 |  12 |         5 |         2 |
|   3 |  14 |         5 |         1 |
|   3 |  15 |         5 |         2 |
|   4 |  12 |         2 |         1 |
|   4 |  17 |         2 |         1 |
+-----+-----+-----------+-----------+


I've created a fiddle at: http://sqlfiddle.com/#!9/e3ebe1/12
I removed one of the subqueries from yours but fundamentally as mysql doesn't support count over partition etc, it will need more than one pass through the table:

Select test.mid, test.pid, A.cnt as midCount, count(*) as pidCount
from test join
(Select mid, count(*) as cnt from test group by mid) A
on test.mid =  A.mid
Group by mid, pid