webrunner (7/26/2010)

---

Good question, interesting to learn that the parentheses make no difference for SELECT DISTINCT.

I have one question, though. I've seen people show me queries where they write SELECT COUNT(DISTINCT userid) or something like that. Is that also the same as SELECT DISTINCT COUNT(userid), or is there a difference?

And I am also curious about how (1) SELECT COUNT(DISTINCT userid) would differ from (2) SELECT COUNT(userid) - I assume if there are multiple rows with the same userid in the table being queried, the second query would return more rows than the first? **

** Edited again - sorry, I guess these queries would return the same number of rows (e.g. one summary row), but maybe different COUNT values. Is that correct?

Thanks,
webrunner

DISTINCT

Specifies that only unique rows can appear in the result set. Null values are considered equal for the purposes of the DISTINCT keyword.

bitbucket-25253 (7/26/2010)

---

webrunner
Easy enough to find out ...

CREATE TABLE #T(userid INT)
INSERT INTO #T 
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 

SELECT COUNT(DISTINCT userid) AS '(1)' FROM #T
SELECT DISTINCT COUNT(userid)AS '(2)' FROM #T

Results:

(1)   (2)
 7     8

Luke L (7/26/2010)

---

AS per The Count() BOL Entry
The Distinct Keyword is a argument of the Count() Function and is not the same as when used like SELECT DISTINCT col1, col2 from mytable;
For that use reference the SELECT Clause BOL Entry that states:

webrunner (7/26/2010)

---

I have one question, though. I've seen people show me queries where they write SELECT COUNT(DISTINCT userid) or something like that. Is that also the same as SELECT DISTINCT COUNT(userid), or is there a difference?

bitbucket-25253 (7/26/2010)

---

webrunner
Easy enough to find out ...

CREATE TABLE #T(userid INT)
INSERT INTO #T 
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 

SELECT COUNT(DISTINCT userid) AS '(1)' FROM #T
SELECT DISTINCT COUNT(userid)AS '(2)' FROM #T

Results:

(1)   (2)
 7     8

Tom.Thomson (7/26/2010)

---

Looks wrong to me. Surely that 8 should be a 1, or the DISTINCT keyword ahould not be present in the second select?

Hugo Kornelis (7/26/2010)COUNT(*) returns the number of columns (regardless of values);