CODEWITHSUNDEEP
mysql
user1016265
user1016265

Reputation: 2397

count unique value in whole table [MySQL]

I have the following table:

+-------+------------------+-----------------------+-------------------+------+
|    id |             A_id |                  B_id |              C_id | age  |
+-------+------------------+-----------------------+-------------------+------+
|    44 |               22 |                    22 |                22 |   35 |
|    47 |                6 |                  NULL |                 6 |   33 |
|    48 |                4 |                  NULL |                 4 |   32 |
|    52 |               23 |                  NULL |                23 |   37 |
|    54 |                9 |                     9 |              NULL |   37 |
|    55 |             NULL |                     8 |              NULL |   29 |
|    60 |                8 |                  NULL |                 8 |   37 |
|    62 |                8 |                  NULL |                 8 |   35 |
|    65 |               11 |                  NULL |                11 |   46 |
|    69 |                9 |                  NULL |                 9 |   52 |
+-------+------------------+-----------------------+-------------------+------+

I'd like to count the total number of rows with certain values, that is:

_id - count(id)
22 - 1
6 - 1
4 - 1
23 - 1
9 - 2
8 - 3
11 - 1

How would I write a query for this?

Upvotes: 2

Views: 61

Answers (1)

sashkello
sashkello

Reputation: 17871

I assume that at least one of the values in each row is not NULL (can be tweaked a bit further to deal with it as well, but I think you wouldn't have any records like this).

It is not very pretty, but should work:

SELECT 
    GREATEST(IFNULL(A_id, 0), IFNULL(B_id, 0), IFNULL(C_id, 0)), 
    COUNT(*)
FROM table 
GROUP BY GREATEST(IFNULL(A_id, 0), IFNULL(B_id, 0), IFNULL(C_id, 0))

Same thing in a bit more compact way:

SELECT 
    IFNULL(A_id, IFNULL(B_id, C_id)),
    COUNT(*)
FROM table 
GROUP BY IFNULL(A_id, IFNULL(B_id, C_id))

Upvotes: 2

Related Questions