Mysql子查询group by错误

Question

我想做一个子查询来找出两个信息。这是我得到的代码

SELECT b.boroughName, SUM(c.crimeCount), 
    (SELECT SUM(c.crimeCount)
    FROM FYP_Borough b JOIN FYP_Crime c on b.boroughID=c.boroughID
    WHERE offenceID=8
    GROUP BY boroughName) AS DrugUse
FROM FYP_Borough b JOIN FYP_Crime c ON b.boroughID=c.boroughID
GROUP BY b.boroughName

这是我得到的错误:子查询返回多于 1 行

我想按 boroughName 进行分组，因为否则它会为我的每个字段提供相同的值。我该怎么做？

Answer 1

SELECT b.boroughName, SUM(c.crimeCount), 
    SUM(CASE WHEN offenceID=8 THEN c.crimeCount ELSE 0 END) 
 AS DrugUse
FROM FYP_Borough b JOIN FYP_Crime c ON b.boroughID=c.boroughID
GROUP BY b.boroughName