CODEWITHSUNDEEP
mysqlsql
user3221861
user3221861

Reputation: 31

MySQL Query to fetch count of values from whole table

I have a table in mysql with values as given value

+--------+---+---+---+---+----+
+ emp_no + A + B + C + D +  E +
+--------+---+---+---+---+----+
+ 100    + 1 + 0 + 2 + 1 +  1 +
+ 101    + 2 + 1 + 1 + 3 +  1 +
+ 102    + 3 + 4 + 4 + 2 +  5 +
+ 103    + 5 + 3 + 4 + 1 +  2 +
+--------+---+---+---+---+----+

Is it possible for me to write a sql query which will fetch me total count of 1,total count of 2,total count of 3....from the aforesaid table.Any help toward this is deeply appreciated.

Thanks

Upvotes: 3

Views: 222

Answers (3)

BlitZ
BlitZ

Reputation: 12168

Possibly SUM() with sum of boolean conditions. Check this out:

SELECT
    SUM(
        (`a` = 1)
        +
        (`b` = 1)
        +
        (`c` = 1)
        +
        (`d` = 1)
        +
        (`e` = 1)
    ) as `1`,
    SUM(
        (`a` = 2)
        +
        (`b` = 2)
        +
        (`c` = 2)
        +
        (`d` = 2)
        +
        (`e` = 2)
    ) as `2`,
    SUM(
        (`a` = 3)
        +
        (`b` = 3)
        +
        (`c` = 3)
        +
        (`d` = 3)
        +
        (`e` = 3)
    ) as `3`,
    SUM(
        (`a` = 4)
        +
        (`b` = 4)
        +
        (`c` = 4)
        +
        (`d` = 4)
        +
        (`e` = 4)
    ) as `4`,
    SUM(
        (`a` = 5)
        +
        (`b` = 5)
        +
        (`c` = 5)
        +
        (`d` = 5)
        +
        (`e` = 5)
    ) as `5`
FROM
    `table`;

Upvotes: 0

Leo Zhao
Leo Zhao

Reputation: 544

Try this:

SELECT num, SUM(counts) FROM (
SELECT A num, COUNT(A) counts FROM table1 GROUP BY A
union all
SELECT B num, COUNT(B) counts FROM table1 GROUP BY B
union all
SELECT C num, COUNT(C) counts FROM table1 GROUP BY C
union all
SELECT D num, COUNT(D) counts FROM table1 GROUP BY D
union all
SELECT E num, COUNT(E) counts FROM table1 GROUP BY E
) temp 
GROUP BY num

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Yes, but it is a little bit complicated. Here is a way, by unpivoting the values and then aggregating:

select val, count(*)
from (select emp_no,
             (case when n.n = 1 then A
                   when n.n = 2 then B
                   when n.n = 3 then C
                   when n.n = 4 then D
                   when n.n = 5 then E
              end) as val
      from table t cross join
           (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
           ) n
     ) e;

Upvotes: 0

Related Questions