SQL join returns fewer rows than non-join-CodePudding

I'm using SQL to work with two tables in a database. The tables contain data from a marketing platform. I have checked within the platform and the data matches up when I use the below query.

SELECT 
COUNT(*),
SUM(a.impressions),
SUM(a.clicks),
SUM(a.spend)
FROM table_1

When I use the below query, to access the name value from table_2 and apply this to matching entries from table_1, the query returns fewer rows - so the values for impressions, spend and clicks are underreporting. I've tried switching the tables round but this hasn't worked and I'm confused as to why I'm consistently getting an incorrect result.

SELECT COUNT(*), SUBQUERY.name, SUM(SUBQUERY.imp) as imp, SUM(SUBQUERY.clicks) as clicks, SUM(SUBQUERY.spend) as spend from
(
SELECT 
a.impressions as imp,
a.clicks as clicks,
a.spend as spend,
b.name as name
FROM table_1 as a
LEFT JOIN table_2 b
ON a.account_id = b.id  
GROUP BY 
imp,
clicks,
spend, 
name, 
date
) AS SUBQUERY
GROUP BY SUBQUERY.name

Example data:

table_1

|customer_id | source | campaign_name| campaign_id |  date      |currency | clicks | impressions | spend |
|----------- |--------| ------------ | ----------- | ---------- | ------- | ------ | ----------- | ----- |
| 1234       | google | campaign_1   | 1090        | 2022-01-01 | GBP     | 10     | 23          | 3     |
| 1642       | google | campaign_3   | 1092        | 2022-12-12 | GBP     | 101    | 222         | 36    |

table_2
|customer_id | customer_name |
|----------- | ------------- |
| 1234       | client_1      |
| 1642       | client_2      |

Edit: have tested the answers, both actually end up creating a large number of duplicates. I've removed date from the query to simplify things

CodePudding user response：

Your first GROUP BY imp,clicks,spend, name, date may be "grouping" rows. Your second GROUP BY SUBQUERY.name will only count what is left as different groups

Hard to tell what you are trying to do, but try removing the inner GROUP BY and see if this is closer to what you expect?

SELECT COUNT(*), SUBQUERY.name, SUM(SUBQUERY.imp) as imp, SUM(SUBQUERY.clicks) as clicks, SUM(SUBQUERY.spend) as spend from
(
SELECT 
a.impressions as imp,
a.clicks as clicks,
a.spend as spend,
b.name as name,
a.date as date
FROM table_1 as a
LEFT JOIN table_2 b
ON a.account_id = b.id  
) AS SUBQUERY
GROUP BY SUBQUERY.name

CodePudding user response：

Seems the simplest

SELECT COUNT(*), name, SUM(imppressions) as imp, SUM(clicks) as clicks, SUM(spend) as spend
FROM table_1 a
LEFT JOIN table_2 b
  ON a.account_id = b.id  
GROUP BY a.name