How to create Mysql Query with "Case When" and "Group By"

Question

Given 3 tables in my database (prodi, mahasiswa and status), I'm trying to output results grouped and counted.

database in this image

Query

SELECT
prodi.namaprodi,
(case when count(`status`.idsm)='1' then 1 else 0 end) as '1',
(case when count(`status`.idsm)='2' then 1 else 0 end) as '2',
(case when count(`status`.idsm)='3' then 1 else 0 end) as '3',
(case when count(`status`.idsm)='4' then 1 else 0 end) as '4',
(case when count(`status`.idsm)='5' then 1 else 0 end) as '5',
(case when count(`status`.idsm)='6' then 1 else 0 end) as '6',
(case when count(`status`.idsm)='7' then 1 else 0 end) as '7',
(case when count(`status`.idsm)='8' then 1 else 0 end) as '8'
FROM
`status`
INNER JOIN mahasiswa ON mahasiswa.idm = `status`.idm
INNER JOIN prodi ON prodi.idp = mahasiswa.idp
GROUP BY
prodi.idp, `status`.idm

Actual Result

Hukum   0   1   0   0   0   0   0   0
Hukum   0   0   0   0   0   1   0   0
Hukum   0   1   0   0   0   0   0   0
Ekonomi 0   0   0   1   0   0   0   0
Ekonomi 0   0   0   1   0   0   0   0

Desired result

Hukum   0   2   0   0   0   1   0   0
Ekonomi 0   0   0   2   0   0   0   0

Any ideas how to get this query to work?

You may need Mysql Pivot table check the link [Mysql Pivot](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — balakrishnan, Oct 08 '18 at 14:15

score 0 · Answer 1 · answered Oct 11 '18 at 05:40

Put it in a subquery before grouping it

SELECT
sesuatu.namaprodi,
sum(sesuatu.1) '1',
sum(sesuatu.2) '2',
sum(sesuatu.3) '3', 
sum(sesuatu.4) '4',
sum(sesuatu.5) '5',
sum(sesuatu.6) '6',
sum(sesuatu.7) '7',
sum(sesuatu.8) '8'
FROM
(SELECT
prodi.namaprodi,
(case when (`status`.idsm)='1' then 1 else 0 end) as '1',
(case when (`status`.idsm)='2' then 1 else 0 end) as '2',
(case when (`status`.idsm)='3' then 1 else 0 end) as '3',
(case when (`status`.idsm)='4' then 1 else 0 end) as '4',
(case when (`status`.idsm)='5' then 1 else 0 end) as '5',
(case when (`status`.idsm)='6' then 1 else 0 end) as '6',
(case when (`status`.idsm)='7' then 1 else 0 end) as '7',
(case when (`status`.idsm)='8' then 1 else 0 end) as '8'
FROM
`status`
INNER JOIN mahasiswa ON mahasiswa.idm = `status`.idm
INNER JOIN prodi ON prodi.idp = mahasiswa.idp)
sesuatu group by sesuatu.namaprodi

http://www.sqlfiddle.com/#!9/bf9dc9/10

score 0 · Accepted Answer · answered Oct 11 '18 at 14:21

The query need to be written as

SELECT
    prodi.namaprodi, 
    SUM(Sem1) as Sem1,
    SUM(Sem2) as Sem2,
    SUM(Sem3) as Sem3,
    SUM(Sem4) as Sem4,
    SUM(Sem5) as Sem5,
    SUM(Sem6) as Sem6,
    SUM(Sem7) as Sem7,
    SUM(Sem8) as Sem8
FROM mahasiswa
INNER JOIN 
(SELECT status.idm, 
    (case when count(`status`.idsm)='1' then 1 else 0 end) as 'Sem1', 
    (case when count(`status`.idsm)='2' then 1 else 0 end) as 'Sem2', 
    (case when count(`status`.idsm)='3' then 1 else 0 end) as 'Sem3', 
    (case when count(`status`.idsm)='4' then 1 else 0 end) as 'Sem4', 
    (case when count(`status`.idsm)='5' then 1 else 0 end) as 'Sem5', 
    (case when count(`status`.idsm)='6' then 1 else 0 end) as 'Sem6', 
    (case when count(`status`.idsm)='7' then 1 else 0 end) as 'Sem7', 
    (case when count(`status`.idsm)='8' then 1 else 0 end) as 'Sem8' 
    FROM `status` 
    INNER JOIN mahasiswa ON mahasiswa.idm = `status`.idm 
    GROUP BY `status`.idm 
)SemCount
    ON mahasiswa.idm = SemCount.idm
INNER JOIN prodi ON prodi.idp = mahasiswa.idp
GROUP BY prodi.idp

I checked the query and it gives the desired result.

When you need to group by more than one columns make a group in sub query and another one in main query

How to create Mysql Query with "Case When" and "Group By"

Query

Actual Result

Desired result

2 Answers2