Mysql Count multiple columns based on the same values

Question

I have the following like table

    A               B
    Yes             OOS
    No              No
    OOS             Yes
    OOS             No
    Yes             No

I want to do the following

Criteria        A               B
Yes             2               1
No              1               3
OOS             2               1

I can get this right with one column like

Criteria        A 
Yes             2 
No              1 
OOS             2

Here is what I have to achieve the above:

SELECT A, count(A) FROM temp_db GROUP BY A;

This might be useful: http://stackoverflow.com/questions/7674786/mysql-pivot-table — Patrick Hofman, Aug 14 '14 at 15:25

score 1 · Answer 1 · answered Aug 14 '14 at 15:35

For this sample data, you could do this with a join of derived tables:

SELECT qa.Criteria, qa.A, qb.B FROM
(SELECT A AS Criteria, count(A) AS A FROM temp_db GROUP BY A) qa
FULL OUTER JOIN 
(SELECT B AS Criteria, count(B) AS B FROM temp_db GROUP BY B) qb
  ON qa.Criteria=qb.Criteria

But if there are missing criteria in the A column, they will not appear in the results of this query, and you would need the UNION ALL approach others have suggested.

score 0 · Accepted Answer · answered Aug 14 '14 at 15:26

You need to get the values into a single column, so you can use group by. Here is one method:

select criteria, sum(A) as A, sum(B) as B
from ((select A as criteria, 1 as A, 0 as B
       from liketable
      ) union all
      (select B, 0, 1
       from liketable
      )
     ) t
group by criteria;

Using the union all approach is the safest way in MySQL, in case not all criteria are in both columns. The following is a slight tweak that might be a bit better performance-wise:

select criteria, sum(A) as A, sum(B) as B
from ((select A as criteria, count(*) as A, 0 as B
       from liketable
       group by A
      ) union all
      (select B, 0, count(*)
       from liketable
       group by B
      )
     ) t
group by criteria;

Often doing two aggregations on half the data is more efficient than a bigger aggregation on all the data.

score 0 · Answer 3 · answered Aug 14 '14 at 15:28

0

select val, sum(cntA), sum(cntB)
from (SELECT A as val, count(A) as cntA,        0 as cntB FROM temp_db GROUP BY A;
      union all
      SELECT B as val,        0 as cntA, count(B) as cntB FROM temp_db GROUP BY B)
group by val;

answered Aug 14 '14 at 15:28

Conffusion

4,335
2
16
28

2

Your answer has appeared in the low quality posts queue. Please attach an explanation as to why / how this fixes OPs problem. – Justin Wood Aug 14 '14 at 15:55
This query is a correct solution for the OPs problem. So please explain why this post is put in the low quality queue. – Conffusion Aug 15 '14 at 11:20
As previously stated, you haven't explained how this works or why it addresses the OPs needs. – Justin Wood Aug 15 '14 at 15:39

Mysql Count multiple columns based on the same values

3 Answers3