mysql - 如何修复来自不同组的总和的除法

Question

任何人都可以帮助我，如果我输入“GROUP BY DATE”，为什么这段代码不起作用

DATE的样本值为201810,201811,201812,201901等

如果我删除“GROUP BY DATE”，它的工作但是，将对所有行求和我想按日期对所有省份的总用户组求和，有什么想法吗？

SELECT DATE AS dates,
   parProvinces.id AS provinceId, 
   parProvinces.name AS province,   
   sum(totaluser) AS countUser,     
   ( SELECT 
         SUM(totalUser)         
         FROM dsMonthlyDemographies         
         GROUP BY DATE  // <<<<-- this is didn't
   ) AS totalUserPerMonths

FROM dsMonthlyDemographies, parProvinces 
WHERE (provincesId = parProvinces.id) 
GROUP BY provinceId, dates 
ORDER BY dates ASC, provinceId ASC

消息错误:

错误代码:1242子查询返回超过 1 行

更新

这是来自 dsMonthlyDemographies 的示例数据

表 parProvinces

Answer 1

你需要一个标量子查询

SELECT DATE AS dates,
   parProvinces.id AS provinceId, 
   parProvinces.name AS province,   
   sum(totaluser) AS countUser,     
   ( SELECT 
         SUM(totalUser)         
         FROM dsMonthlyDemographies b where a.date=b.date        
   ) AS totalUserPerMonths

FROM dsMonthlyDemographies a join parProvinces on a.provincesId = parProvinces.id
GROUP BY provinceId, dates 
ORDER BY dates ASC, provinceId ASC

或

select * from 
(SELECT DATE AS dates,
   parProvinces.id AS provinceId, 
   parProvinces.name AS province,   
   sum(totaluser) AS countUser     
   FROM dsMonthlyDemographies a join parProvinces on a.provincesId =parProvinces.id
   GROUP BY provinceId, dates 
)A inner join 
( SELECT 
         dates,SUM(totalUser)         
         FROM dsMonthlyDemographies group by dates   
 ) AS B on A.dates=b.dates

注意:最好使用显式连接而不是逗号分隔连接