SQL join causes SUM to multiply

Question

I have a query that returns expected results grouped by week:

SELECT Week(display.date) AS date_granularity,
                `display`.`site`,
                `display`.`ad_unit`,
                Sum(`display`.`revenue`)
FROM   `display`
WHERE `display`.`date` BETWEEN '2022-01-01' AND '2022-12-15'
       AND ( `display`.`ad_unit` = 'IC1' )
GROUP  BY `date_granularity`,
          `display`.`site`,
          `display`.`ad_unit`

Results: results image (Sorry, it wouldn't let me insert the image inline. Blacked out the names of our clients for privacy)

But when I add a join:

SELECT Week(display.date) AS date_granularity,
                `display`.`site`,
                `display`.`ad_unit`,
                Sum(`display`.`revenue`)
FROM   `display`
JOIN `traffic`
               ON Week(`traffic`.`date`) = Week(`display`.`date`)
WHERE `display`.`date` BETWEEN '2022-01-01' AND '2022-12-15'
       AND ( `display`.`ad_unit` = 'IC1' )
GROUP  BY `date_granularity`,
          `display`.`site`,
          `display`.`ad_unit`

The results are heavily multiplied: results image

I'm pulling my hair out here. I've read similar questions where they were advised to use a subquery, but I don't know how to do that. Any advice?

(If some of my syntax looks weird, it's because this query was generated by Laravel)

Why are you joining with `traffic`, you don't use anything from that table? — Barmar, Jun 30 '22 at 19:44
`JOIN (SELECT DISTINCT WEEK(date) AS week FROM traffic) AS t ON t.week = WEEK(display.date)` — Barmar, Jun 30 '22 at 19:45

score 0 · Answer 1 · answered Jun 30 '22 at 19:43

The JOIN actually multiplies the records. If you have 5 rows in display and 7 rows in traffic for a given week, you'll get 5 * 7 = 35 rows. Those are then grouped and summed.

Guessing what you might be up to, one of the following approaches might work:

Either tighten the filter condition so there is only one row in traffic per week

or join after the aggregation, that is with a subquery:

SELECT ...
FROM (SELECT ... FROM display ... GROUP BY ... /* your current query */)
    AS grouped
JOIN traffic ON Week(traffic.date) = Week(grouped.date)

SQL join causes SUM to multiply

1 Answers1