MySQL count 2 different columns both related to one id

I have some users like user 10, 20 and 30; and the may be registered on column a or column b;
I need to count how many rows on column a are on each user (simple count() ) but I also want to count how many rows are on the same user but on column b;
my challenge is that I have different users in columns a and b on same row.

+----+----+------+------+
| id | a  | b    | c    |
+----+----+------+------+
|  1 | 10 | NULL | NULL |
|  2 | 10 |   20 | NULL |
|  3 | 20 | NULL | NULL |
|  4 | 10 |   30 | NULL |
|  5 | 20 | NULL | NULL |
|  6 | 30 |   20 | NULL |
|  7 | 10 |   20 | NULL |
+----+----+------+------+

I have tried:

select a, count(if(a is not null,1,null)) aC, count(if(b is not null, 1, null)) bC 
from test 
group by a;

but it returns on column b if is not null:

+----+----+----+
| a  | aC | bC |
+----+----+----+
| 10 |  4 |  3 |
| 20 |  2 |  0 |
| 30 |  1 |  1 |
+----+----+----+

what I need is

+----+----+----+
| a  | aC | bC |
+----+----+----+
| 10 |  4 |  0 |
| 20 |  2 |  3 |
| 30 |  1 |  1 |
+----+----+----+

I have managed to get the result with a nested select with a where, but only works if I query for a specific user (only one row as result).

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

here is one way :

select a 
    , count(case when col ='A' then 1 end) Acount
    , count(case when col ='B' then 1 end) BCount
from (
   select a , 'A' col
   from tablename
   union all 
   select b , 'B'
   from tablename 
) t 
group by a

Method 2

Hmm, that may indicate a bad design…

What you can try is to use derived tables here. First you need one with all users using a UNION over all a‘s and b‘s (if you have a table storing all users you can use that instead). Then left join two derived tables doing the aggregation group by a or b respectively.

SELECT u.user,
       coalesce(a.count, 0) AS ac,
       coalesce(b.count, 0) AS bc
       FROM (SELECT a AS user
                    FROM test
             UNION
             SELECT b AS user
                    FROM test) u
            LEFT JOIN (SELECT a AS user,
                              count(*) AS count
                              FROM test
                              GROUP BY a) a
                      ON a.user = u.user
            LEFT JOIN (SELECT b AS user,
                              count(*) AS count
                              FROM test
                              GROUP BY b) b
                      ON b.user = u.user;


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x