SQL : Query with multiple COUNT() and Specific Conditions

Question

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 Name Column
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!

Stu · Accepted Answer · 2022-12-13 09:39:17Z

1

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;

answered Dec 13, 2022 at 9:39

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL : Query with multiple COUNT() and Specific Conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related