Help mysql grouping

Question

Please help me figure a single query that will transform the data below...

|id |status_a |status_b |
|+++++++++++++++++++++++|
|  1|active   |inactive |

...into this one.

|status_group      |count|
|++++++++++++++++++++++++|
|status_a.active   |    1|
|status_b.inactive |    1|

edit: If a single pass query is possible then that will be better. Also, does a query with unions does a single pass?

Answer 1

I have a solution that uses UNIONs. Shown here:

SELECT 'status_a.active' AS status_group, COUNT(*) AS count FROM `test` WHERE status_a = 'active'
UNION
SELECT 'status_a.inactive' AS status_group, COUNT(*) AS count FROM `test` WHERE status_a = 'inactive'
UNION
SELECT 'status_b.active' AS status_group, COUNT(*) AS count FROM `test` WHERE status_b = 'active'
UNION
SELECT 'status_b.inactive' AS status_group, COUNT(*) AS count FROM `test` WHERE status_b = 'inactive'

Basically, it queries each condition for status_a or status_b being active or not. We get four such queries and we apply UNION to all of them.

Answer 2

I suppose, I've to move my comment a while ago which is also a shorter solution here than hw's.

SELECT CONCAT('status_a.', status_a) AS stat, COUNT(id) FROM base GROUP BY stat 
UNION
SELECT CONCAT('status_b.', status_b) AS stat, COUNT(id) FROM base GROUP BY stat

Answer 3

If status can be either only active or inactive, I'd suggest a different approach:

SELECT
    sum(if(status_a='active',1,0)) AS status_a_active,
    sum(if(status_a='inactive',1,0)) AS status_a_inactive,
    sum(if(status_b='active',1,0)) AS status_b_active,
    sum(if(status_b='inactive',1,0)) AS status_b_inactive
FROM table

Otherwise you need to use the UNION approach, but I'd do it a little differently. First, you can use UNION ALL, because you don't need to remove duplicates in the result. I'd also use GROUP BY only once like this:

SELECT status_group, count(id)
FROM (
    SELECT CONCAT('status_a.', status_a) AS status_group, id FROM table
    UNION ALL
    SELECT CONCAT('status_b.', status_b) AS status_group, id FROM table
) a
GROUP BY status_group