Edited to clarify and simplify
I have the following query
select
Signups.user_id,
Count(timeoutTable.user_id) as timeoutCount,
Count(confirmedTable.user_id) as confirmedCount
from Signups
left join (
select * from Confirmations where action = 'timeout'
) as timeoutTable on signups.user_id = timeoutTable.user_id
left join (
select * from Confirmations where action = 'confirmed'
) as confirmedTable on signups.user_id = confirmedTable.user_id
group by
Signups.user_id,
timeoutTable.user_id,
confirmedTable.user_id
When run with the following input
Signups Table
| user_id | time_stamp |
| ------- | ------------------- |
| 15 | 2020-07-31 18:26:35 |
| 16 | 2021-05-20 01:38:09 |
| 7 | 2020-08-02 08:45:14 |
| 10 | 2020-06-24 17:13:14 |
| 5 | 2020-06-27 17:59:29 |
| 9 | 2021-11-08 03:05:14 |
| 8 | 2021-12-13 03:38:58 |
| 12 | 2020-09-16 11:17:39 |
Confirmations Table
| user_id | time_stamp | action |
| ------- | ------------------- | --------- |
| 7 | 2020-03-31 13:11:43 | timeout |
| 7 | 2021-03-25 07:40:25 | timeout |
| 8 | 2020-07-27 19:43:25 | confirmed |
| 8 | 2021-03-07 19:48:06 | timeout |
| 7 | 2020-01-24 15:43:47 | confirmed |
It outputs:
| user_id | timeoutCount | confirmedCount |
| ------- | ------------ | -------------- |
| 15 | 0 | 0 |
| 16 | 0 | 0 |
| 7 | 2 | 2 |
| 10 | 0 | 0 |
| 5 | 0 | 0 |
| 9 | 0 | 0 |
| 8 | 1 | 1 |
| 12 | 0 | 0 |
I am trying to make it so that user_id 7 has a confirmed of 1 and a timeout of 2 but it sets both values to 2 for some reason. Any help would be greatly appreciated.
2
Answers
Your problem is that you are joining all timeouts to all confirmations. So, for a signup with 2 timeouts and 3 confirmations, you’ll get 2 x 3 = 6 rows that you then aggregate.
When aggregating more than one table, aggregate before joining:
An alternative to this is conditional aggregation here:
To see what’s happening, look at the subquery without the group by.
Note how user_id 7 has 2 rows and each have a timeout and a confirmation. These rows are counted as both a timeout and a confirmation. This is because you’re joining with two tables at the same time.
If we instead do the aggregations first and then join, the counts work out.
Demonstration
Note:
is better written as without the subquery.