SQLTeam.com | Weblogs | Forums

Query that accounts for Events and Non Events

 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.

SELECT
    racedate,
    SUM(CASE WHEN winpos=1 and totalrank=1 THEN 1 ELSE 0 END) AS winpos_totalrank_1_count,
    SUM(CASE WHEN ISNULL(winpos, 0) <> 1 OR ISNULL(totalrank, 0) <> 1 THEN 1 ELSE 0 END) AS other_count
FROM t_stats
GROUP BY racedate
ORDER BY racedate
2 Likes

Thank you so much, works like a charm.

You're welcome very much :-).