Sql Server 2005 using count and distinct together

Question

I'm trying to get the count of each distinct field in my database. For example, we are using something called sourceCodes - I want to be able to see how many of each different sourceCode there is in my database. So far, I have this

SELECT sourceCode, COUNT(DISTINCT sourceCode) 
FROM [SecureOrders] 
WHERE DateTime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) 
  AND DateTime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1) 
GROUP BY sourceCode

(I'm trying to display the name of the sourceCode first, and then the count). So far though, the only thing I ever get in my second column is "1"...and I'm positive there are more than one. I know I worded this question really poorly, but I can't really figure out any other way to say it. Can anybody see why this is happening?

Answer 1

The "distinct" in your sample is not being applied at the correct place. By grouping by SourceCode, you are already getting distinct values from that column.

So, you only need to count the rows in each group:

SELECT sourceCode, COUNT(*) 
FROM [SecureOrders] 
WHERE DateTime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) 
  AND DateTime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1) 
GROUP BY sourceCode

Answer 2

Remove the DISTINCT:

SELECT sourceCode, COUNT(sourceCode) 
FROM [SecureOrders] 
WHERE DateTime >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)   
  AND DateTime < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 1) 
GROUP BY sourceCode