MySQL查询只返回一行，因为其中包含求和函数

Question

我试图创建一个广告查询，在其中，我想获取每个广告的所有印象的数据。一个用户可以有多个广告和印象将计算在一个表格的基础上，每天。所以每一天我都会有一个不同的排。下面是我的查询当前的样子。

SELECT  
   eac.id,
   eac.gender,
   eac.start_date,
   eac.end_date,
   eac.ad_image_path,
   eac.ad_link,
   eac.requestfrom,
   eac.traffic,
   eac.registertype,
   eacr.region_id,
   eac.active,
   eac.impression,
   eac.center_image_path,
   eac.bottom_image_path,
   eac.approved_by,
   er.name as country_name,
   eac.budget,
   sum(budget/ (DATEDIFF(end_date,start_date)) *1000) as daily_imp,  
   eaa.impression_count,
   eac.customer_id,
   eaa.created_at
FROM 
   `enrich_advert_customer` eac         
   JOIN `enrich_advert_customer_regions` eacr ON eac.id = eacr.advert_customer_id  
   JOIN `enrich_regions` er ON er.id = eacr.region_id
   LEFT JOIN `enrich_advert_abstract` eaa on eac.id = eaa.advert_customer_id        
WHERE 
   eac.requestfrom ='web' AND
   eac.registertype = 'paid' AND
   eac.active = 1 AND
   eac.approved_by = 1 AND
   eac.gender ='male' AND
   er.name = 'india' AND
   eac.start_date <= '2018-11-5' AND
   eac.end_date >= '2018-11-10'

但问题是如果我用

sum(budget/ (DATEDIFF(end_date,start_date)) *1000) as daily_imp 

然后，它一次只返回一行。

如果你能建议我在哪里犯错误，那会很有帮助的。

谢谢!

Answer 1

在应用聚合时，需要在其中添加group by子句和其他列。

SELECT  eac.id,eac.gender,eac.start_date,eac.end_date,eac.ad_image_path,eac.ad_link,eac.requestfrom,eac.traffic,eac.registertype,eacr.region_id,eac.active,eac.impression,eac.center_image_path,eac.bottom_image_path,eac.approved_by,er.name as country_name,eac.budget,sum(budget/ (DATEDIFF(end_date,start_date)) *1000) as daily_imp  ,eaa.impression_count,eac.customer_id,eaa.created_at

FROM 
    `enrich_advert_customer` eac 

JOIN 
    `enrich_advert_customer_regions` eacr
   ON eac.id = eacr.advert_customer_id 

JOIN 
    `enrich_regions` er
   ON er.id = eacr.region_id

LEFT JOIN 
    `enrich_advert_abstract` eaa
    on eac.id = eaa.advert_customer_id

WHERE eac.requestfrom ='web' AND
      eac.registertype = 'paid' AND
      eac.active = 1 AND
      eac.approved_by = 1 AND
      eac.gender ='male' AND
      er.name = 'india' AND
      eac.start_date <= '2018-11-5' AND
      eac.end_date >= '2018-11-10'
group by eac.id,eac.gender,eac.start_date,eac.end_date,eac.ad_image_path,eac.ad_link,eac.requestfrom,eac.traffic,eac.registertype,eacr.region_id,eac.active,eac.impression,eac.center_image_path,eac.bottom_image_path,eac.approved_by,er.name,eac.budget,  eaa.impression_count,eac.customer_id,eaa.created_at