Avoid unnecessary fields at group by

Question

I have three tables:

1. Campaign

id	budget
345	1650
346	2320

2. Report

campaign_id	ad_id	cost	deals	date
345	1	45	2	2022-01-01
345	2	20	10	2022-01-01
345	1	70	0	2022-01-02
346	1	100	3	2022-01-01
346	2	250	6	2022-01-01

3. Reach

campaign_id	reach	date
345	110	2022-01-01
346	90	2022-01-01

I want to JOIN field budget from table #1 to table #2 and JOIN field reach from table #1 to table #1, and GROUP it by campaign_id and date. So I need to SUM(cost) and SUM(deals), but what about budget and reach from another tables? I can't aggregate them, but also I don't want to add these fields to GROUP BY because logically it's not correct I guess.

So how can I join these tables and avoid unnecessary fields at GROUP BY statement?

Result should be:

campaign_id	cost	deals	date	budget	reach
345	65	12	2022-01-01	1650	110
345	70	0	2022-02-01	1650	NULL
346	350	9	2022-01-01	2320	90

Answer 1

What you need is to use LEFT JOIN while joining Reach table to the others along with matching campaign_id and date columns while including reach column within the GROUP BY list such as

SELECT r.campaign_id, 
       SUM(cost) AS cost, SUM(deals) AS deals, r.date, MAX(budget) AS budget, reach
  FROM Campaign AS c
  JOIN Report AS r
    ON c.id = r.campaign_id
  LEFT JOIN Reach AS re
    ON c.id = re.campaign_id
   AND re.date = r.date 
 GROUP BY r.campaign_id, r.date, reach 
 ORDER BY r.campaign_id, r.date 

Demo