Distinct vs Count(*) = 1

[fajitapete]

Help!! I am at a loss, thought I new some of this but havent a clue as to what is wrong here.

SELECT distinct values
FROM LOGGING.DBO.FIRST_CALL

this yeilds a count of 150432
but if I say

SELECT values, count(values) as counted
FROM LOGGING.DBO.FIRST_CALL
group by values
having count(values) = 1

this yeilds a count of 123478

Icheck for nulls and other than that I can not determine why the counts are different. HELP and thanks

 

[SonOfEmidec1100]

Looks like duplicate values to me. The first one will select duplicates once, the second one excludes them as count(values) > 1

cjw

 

[tlbroadbent]

The first query is selecting all unique values. This would include values that appear multiple times in the table.

The second query is selecting the values that appear only once in the table. I would expect different results.

Bottom line - Nothing is wrong here except your understanding. I hope this helps to clear the matter for you. Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[fajitapete]

DUH - I'll go stand in the corner. Sometimes I cna't belive how stupid I can be, Thanks Terry