MySQL query returning only one row because of SUM function in it

1997 views mysql
10

I am trying to create an advertisement query where I want to fetch data of all the impressions per advertisement. One user can have multiple advertisements and impressions will be counted in a table on per day basis. So for each day I will have one different row. Here is how my query currently looks like.

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'

But the problem here is if I am using sum(budget/ (DATEDIFF(end_date,start_date)) *1000) as daily_imp this then its returning only one row at a time.

If you can suggest where I am making a mistake that will be helpful.

Thank you!

answered question

1 Answer

4

You need to add group by clause and others column in there

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 as country_name,eac.budget,  ,eaa.impression_count,eac.customer_id,eaa.created_at

posted this

Have an answer?

JD

Please login first before posting an answer.