CODEWITHSUNDEEP
mysqldatabase
indi60
indi60

Reputation: 907

sum of distinct value count from one table

I getting problem to select sorting data. This i explain my problem:

table student:

id  name    degree
1   Adam    11
2   Adam    12
3   Beny    11
4   Beny    13
5   Charly  12
6   Adam    11

i wanna get the result like this:

name    degree 11   degree 12   degree 13
Adam    2           1            0
Beny    1           0            1
Charly  0           1            0

i confused the way to get it result.. i tried using join, union and still didnt get the solution. Please give me suggestion to solve it. Thanks

Upvotes: 0

Views: 89

Answers (2)

chetan
chetan

Reputation: 2886

select name, 
       sum(if(degree=11,1,0)) "degree 11",
       sum(if(degree=12,1,0)) "degree 12",
       sum(if(degree=13,1,0)) "degree 13" 
from student
group by name;

Upvotes: 0

John Woo
John Woo

Reputation: 263693

if you have known number of degree, this could be done using static query,

SELECT  Name,
        SUM(CASE WHEN degree = 11 THEN 1 ELSE 0 END) `degree 11`,
        SUM(CASE WHEN degree = 12 THEN 1 ELSE 0 END) `degree 12`,
        SUM(CASE WHEN degree = 13 THEN 1 ELSE 0 END) `degree 13`
FROM    tableName
GROUP   BY Name

OUTPUT

╔════════╦═══════════╦═══════════╦═══════════╗
║  NAME  ║ DEGREE 11 ║ DEGREE 12 ║ DEGREE 13 ║
╠════════╬═══════════╬═══════════╬═══════════╣
║ Adam   ║         2 ║         1 ║         0 ║
║ Beny   ║         1 ║         0 ║         1 ║
║ Charly ║         0 ║         1 ║         0 ║
╚════════╩═══════════╩═══════════╩═══════════╝

Otherwise, if you have unknown number of degree, a much more preferred solution is to create a dynamic query.

SELECT  GROUP_CONCAT(DISTINCT
        CONCAT('SUM(CASE WHEN degree = ',
               degree,
               ' THEN 1 ELSE 0 END) AS ',
               CONCAT('`degree ', degree, '`')
               )) INTO @sql
FROM Tablename;

SET @sql = CONCAT('SELECT  Name, ', @sql, ' 
                  FROM    tableName
                  GROUP   BY Name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Upvotes: 6

Related Questions