How to Fix Divide on sum from different group by

问题: anyone can help me, why this code didn't work if I put "GROUP BY DATE" sample value of DATE is 201810,201811,201812,201901 etc if I remove "GROUP BY DATE", its work but...

问题:

anyone can help me, why this code didn't work if I put "GROUP BY DATE"

sample value of DATE is 201810,201811,201812,201901 etc

if I remove "GROUP BY DATE", its work but, will SUM all row I want SUM totaluser group by all provinces by date, any ideas?

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

message error:

Error Code: 1242 Subquery returns more than 1 row

UPDATE

this is sample data from dsMonthlyDemographies

table dsMonthlyDemographies

table parProvinces

table parProvinces


回答1:

You need a scalar subquery

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

OR

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

NOTE: It's always best to use explicit join rather than a comma separated join


回答2:

Hi friend you just add b.provincesId = dsMonthlyDemographies.provincesId like below:

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


    FROM dsMonthlyDemographies, parProvinces 
    WHERE (provincesId = parProvinces.id) 
    GROUP BY provinceId, dates 
    ORDER BY dates ASC, provinceId ASC
  • 发表于 2019-05-19 21:38
  • 阅读 ( 133 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除