在分组前将其放入子查询中

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型

查询需要写成

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

我检查了查询，它给出了所需的结果。
当需要按多个列分组时，在子查询中创建一个组，在主查询中创建另一个组