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: stackoverflow.com/questions/7674786/mysql-pivot-table — Patrick Hofman
– Patrick Hofman, Commented Aug 14, 2014 at 15:25

Tab Alleman · Accepted Answer · 2014-08-14 15:35:27Z

1

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.

answered Aug 14, 2014 at 15:35

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-08-14 15:26:42Z

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.

Conffusion · Accepted Answer · 2014-08-14 15:28:36Z

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, 2014 at 15:28

Conffusion

4,5152 gold badges19 silver badges30 bronze badges

3 Comments

Justin Wood Over a year ago

Your answer has appeared in the low quality posts queue. Please attach an explanation as to why / how this fixes OPs problem.

Conffusion Over a year ago

This query is a correct solution for the OPs problem. So please explain why this post is put in the low quality queue.

Justin Wood Over a year ago

As previously stated, you haven't explained how this works or why it addresses the OPs needs.

Collectives™ on Stack Overflow

Mysql Count multiple columns based on the same values

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related