TSQL count distinct

Question

I am trying to get the unique values out from a table, the table holds the following as a time log file:

id | time | code | user
1  | 7000 | xxxx | 1 
2  | 7000 | xxxx | 1
3  | 7500 | xxxx | 2
4  | 7000 | xxxx | 3

What I would like to know is how many unique users have used the code at time, e.g. 7000, it should say 2 but with the distinct I write I get 3

SELECT Time, COUNT(*) as total, Code
FROM dbo.AnalyticsPause
WHERE CODE = 'xxxx'
GROUP BY  id, Time, Code

Result:

time | Count | code 
7000 | 3     | xxxx
7500 | 1     | xxxx

where I would like to have

time | Count | code 
7000 | 2     | xxxx
7500 | 1     | xxxx

How would I be able to add a distinct on the id_user and still count all the time together

Solution

count(*) counts the total number of rows (in the group). You should instead just count the distinct user:

SELECT   Time, COUNT(DISTINCT user) as total, Code
FROM     dbo.AnalyticsPause
WHERE    code = 'xxxx'
GROUP BY Time, Code

Comments

[Ewald Bos]

i think this works, need to run some test on the actual code but it seems promissing :)

[Carsten Massmann]

Leave out id from the group by clause, then it should work. The code can also be removed from the group by clause, as it is already used in the where clause.