1

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

0

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
0

ALTERNATE QUERY

I have a query that will do the following

  • Only one subquery
  • One GROUP BY ... WITH ROLLUP
  • Here is the catch: the mid count is a separate row instead of a column

Here is the query

SELECT mid,IFNULL(pid,0) pid,knt count FROM
(
    SELECT mid,pid,COUNT(id) knt FROM test
    GROUP BY mid,pid WITH ROLLUP
) A WHERE mid IS NOT NULL
ORDER BY mid,IFNULL(pid,0)=0;

Here is the output

mysql> SELECT mid,IFNULL(pid,0) pid,knt count FROM
    -> (
    ->     SELECT mid,pid,COUNT(id) knt FROM test
    ->     GROUP BY mid,pid WITH ROLLUP
    -> ) A WHERE mid IS NOT NULL
    -> ORDER BY mid,IFNULL(pid,0)=0;
+------+-----+-------+
| mid  | pid | count |
+------+-----+-------+
|    3 |  15 |     2 |
|    3 |  12 |     2 |
|    3 |  14 |     1 |
|    3 |   0 |     5 |
|    4 |  12 |     1 |
|    4 |  17 |     1 |
|    4 |   0 |     2 |
+------+-----+-------+
7 rows in set (0.00 sec)

mysql>

Here is what this means

If you ignore the row result with pid 0, you get

+------+----+-------+
| mid  | pd | count |
+------+----+-------+
|    3 | 12 |     2 |
|    3 | 14 |     1 |
|    3 | 15 |     2 |
|    4 | 12 |     1 |
|    4 | 17 |     1 |
+------+----+-------+

These counts match your output for the pid_count

If you look at the row results with pid 0, you get

+------+----+-------+
| mid  | pd | count |
+------+----+-------+
|    3 |  0 |     5 |
|    4 |  0 |     2 |
+------+----+-------+

These counts match your output for the mid_count

Without introducing another subquery, this is my suggested query.

SUMMARY

The output would have to be used as follows

+------+-----+-------+
| mid  | pid | count |
+------+-----+-------+
|    3 |  15 |     2 |
|    3 |  12 |     2 |
|    3 |  14 |     1 |
|    3 |   0 |     5 | <-- Count of mid 3
|    4 |  12 |     1 |
|    4 |  17 |     1 |
|    4 |   0 |     2 | <-- Count of mid 4
+------+-----+-------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.