CODEWITHSUNDEEP
sqlsql-serversql-server-2008distinct
AMC
AMC

Reputation: 321

Count of distinct values per day, excluding reoccuring until value changes

I'm really struggling with how to explain this so I'll try and give you the format of the table below, and the desired outcome.

I have a table which contains a uniqueID, date, userID and result. I'm trying to count the number of results that are 'Correct' per day, but I only want to count unique occurances based on the userID column. I then want to exclude any furhter occurances of 'Correct' for that particular userID, until the result for the userID changes to 'Success'.

UID Date        UserID Result
1   01/01/2014  5    Correct
2   01/01/2014  5    Correct
3   02/01/2014  4    Correct
4   03/01/2014  4    Correct
5   03/01/2014  5    Incorrect
6   03/01/2014  4    Incorrect
7   05/01/2014  5    Correct
8   07/01/2014  4    Correct
9   08/01/2014  5    Success
10  08/01/2014  4    Success

Based on the above data, I'd expect to see the below:

Date        Correct Success
01/01/2014  1       0
02/01/2014  1       0
03/01/2014  0       0
05/01/2014  0       0
07/01/2014  0       0
08/01/2014  0       2

Can anyone help? I'm using SQL Server 2008

Upvotes: 0

Views: 1809

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270583

Use count(distinct) with case:

select date,
       count(distinct case when result = 'Correct' then UserId end) as Correct,
       count(distinct case when result = 'Success' then UserId end) as Success
from data d
group by date
order by date;

EDIT:

The above counts correct on all occurrences. If you only want the first one to be counted:

select date,
       count(case when result = 'Correct' and seqnum = 1 then UserId end) as Correct,
       count(case when result = 'Success' and seqnum = 1 then UserId end) as Success
from (select d.*,
             row_number() over (partition by UserId, result order by Uid) as seqnum
      from data d
     ) d;

In this case, the distinct is unnecessary.

Upvotes: 2

Related Questions