I'm fairly new to SQL and I've been trying to query a table where I need multiple COUNT() with specific conditions, those being:
- Distinct name in the
NameColumn - Based on
Date - Based on a group
Here's a sample of the table:
| Group | Name | Dates |
|---|---|---|
| GR1 | Name1 | 2022-01-01 |
| GR1 | Name1 | 2022-01-01 |
| GR1 | Name2 | 2022-02-01 |
| GR1 | Name3 | 2022-02-01 |
| GR2 | Name4 | 2022-01-01 |
| GR2 | Name5 | 2022-03-01 |
| GR2 | Name5 | 2022-03-01 |
| GR2 | Name6 | 2022-04-01 |
| GR2 | Name7 | 2022-04-01 |
And here's what i'm trying to achieve based on the sample:
| Month | GR1 | GR2 |
|---|---|---|
| 2022-01-01 | 1 | 1 |
| 2022-02-01 | 2 | 0 |
| 2022-03-01 | 0 | 1 |
| 2022-04-01 | 0 | 2 |
Here's the query I've got so far:
SELECT DISTINCT `Dates` AS Month,
(SELECT COUNT(DISTINCT `Name`) FROM `table` WHERE `Group` LIKE '%GR1%') AS GR1,
(SELECT COUNT(DISTINCT `Name`) FROM `table` WHERE `Group` LIKE '%GR2%') AS GR2
FROM `table`
ORDER BY Month
I've got the result I expected from this where i got the number of distinct names but it's not filtered through the dates.
Result:
| DATE | GR1 | GR2 |
|---|---|---|
| 2022-01-01 | 3 | 4 |
| 2022-02-01 | 3 | 4 |
| 2022-03-01 | 3 | 4 |
| 2022-04-01 | 3 | 4 |
I've been struggling to create the condition for the dates while keeping the condition for the Groups
Thanks in advance for any tips or answers you might have! Have a good day!
CodePudding user response：
Instead of selecting distinct rows you need to aggregate by the date and use conditional aggregation to count your group names.
What your query is trying to do won't work since each subquery is looking at all rows, you are not correlating to within the date ranges.
Try the following:
select dates as "Month",
Count(distinct case when "group" = 'GR1' then Name end) GR1,
Count(distinct case when "group" = 'GR2' then Name end) GR2
from t
group by dates;