CREATE TABLE [dbo].[T_Stats](
[eventid] [int] NULL,
[racedate] [date] NULL,
[totalrank] [int] NULL,
[winpos] [int] NULL
)
insert into t_stats
(eventid,racedate,totalrank,winpos)
values
(1,'20210101',1,1)
insert into t_stats
(eventid,racedate,totalrank,winpos)
values
(2,'20210101',1,0)
insert into t_stats
(eventid,racedate,totalrank,winpos)
values
(3,'20210101',1,1)
insert into t_stats
(eventid,racedate,totalrank,winpos)
values
(4,'20210102',1,0)
insert into t_stats
(eventid,racedate,totalrank,winpos)
values
(5,'20210102',1,0)
insert into t_stats
(eventid,racedate,totalrank,winpos)
values
(6,'20210102',1,0)
insert into t_stats
(eventid,racedate,totalrank,winpos)
values
(7,'20210103',1,0)
insert into t_stats
(eventid,racedate,totalrank,winpos)
values
(8,'20210102',1,0)
insert into t_stats
(eventid,racedate,totalrank,winpos)
values
(9,'20210102',1,0)
insert into t_stats
(eventid,racedate,totalrank,winpos)
values
(10,'20210103',1,1)
insert into t_stats
(eventid,racedate,totalrank,winpos)
values
(11,'20210103',1,1)
insert into t_stats
(eventid,racedate,totalrank,winpos)
values
(12,'20210103',1,1)
I am trying to find a count, grouped by date, where totalrank=1 and winpos=1, but on those days
when those two conditions are not met- such on on the date '20210102 '- I want the record to give the date but with a count of 0.
select
count(distinct eventid)
,racedate
from T_Stats
where winpos=1 and totalrank=1
group by racedate
The above query displays the results when both conditions are met, but when all occurrences of winpos are 0 then obviously no results are returned.
All help appreciated. Thanks.
PS I know that my query is only asking for dates and counts where both conditions are met, but I was hoping that something along the lines of a 'case when ' clause or a subquery or cte could catch those times when the dual conditions are not met, but I can't figure it out. Thanks.