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

Answer 1

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