Count of Distinct Records

Question

Post reply

Count of Distinct Records

hawg

SSCertifiable

Points: 6044
More actions
April 1, 2004 at 1:19 pm

#84664

I know I have seen this mentioned before but I can't find any of the discussions so I am asking again.
I've got a table that has a field, FieldA, that contains people's names.
FieldA has duplicates. I want to get a count of the number of DISTINCT entries in FieldA.
How do I do it?
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

You can try this:

SELECT COUNT(*) FROM (SELECT DISTINCT FieldA FROM TableA) AS A

Paul Cassar Ten Centuries Points: 1065 More actions · Answer 2

Hey!

SELECT COUNT( DISTINCT [FieldA] ) FROM Table1

Hope this helps!

Kindest Regards,

hawg SSCertifiable Points: 6044 More actions · Answer 3

Thanks, I thought I had tried it before but I tried it again. I must have had "fat fingers" the first time.

Thanks again.

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

Paul Cassar Ten Centuries Points: 1065 More actions · Answer 4

Hey!

Also, the method racosta posted, and the method I posted should produce identical query plans... it just depends on how you want to do it!

Kindest Regards,

hawg SSCertifiable Points: 6044 More actions · Answer 5

Yes, I did both and got the same results. I admit I had not thought of racosta's method but it works.

Thanks to both of you.

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

Carl B. SSCertifiable Points: 7931 More actions · Answer 6

I everyone,

Just want to add this:

The use of this method:

SELECT COUNT( DISTINCT [FieldA] ) FROM Table1

Will always work if you have one column. But, of course, won't work if you have multiple columns:

SELECT COUNT( DISTINCT [FieldA], [FieldB]&nbsp FROM Table1

The other suggestion will work in all cases:

SELECT COUNT(*) FROM (SELECT DISTINCT FieldA, FieldB, ... FROM TableA) AS A

Best Regards,

Carl

Carl B. SSCertifiable Points: 7931 More actions · Answer 7

Ouups, s'cuse me for the typo error:

Should have read:

SELECT COUNT( DISTINCT [FieldA], [FieldB], ... ) FROM Table1

Carl

shwetha004 SSC Enthusiast Points: 153 More actions · Answer 8

hi all,

i managed to get a result using the above suggestions,but i am not sure hw to interpret it.first of all, wat i want is,to find out how many times a certain value appears in the result set of a query.for example, i have a table,table1, with 3 columns(column1,column2,column3).my query is:

Select * from table1 where column1='val'.

what i wanna do with this result is,to find out how many times a certain value(say 'a') appears in column2, so that i can count the frequency.

when i change my query to SELECT COUNT(*) FROM (SELECT DISTINCT column1, column2, column3 FROM table1) AS A,

i get a number as a result. i think this means there are this number of distinct values in the result.but it still doesnt fulfill my aim.

any suggestions?

ChiragNS One Orange Chip Points: 26137 More actions · Answer 9

Hi

Is this what you want...

SELECT COUNT(*) FROM (SELECT DISTINCT column1, column2, column3 FROM table1 where column2 = 'a') AS A,

"Keep Trying"

shwetha004 SSC Enthusiast Points: 153 More actions · Answer 10

hi all,

thanks chirag for your suggestion. but that code is still giving me a number, which i supposed is the number of distinct entries..

anyway,i found a way to do it.

SELECT column1 ,COUNT(column1) AS expr1

FROM (SELECT * FROM table1 WHERE column2='a')

GROUP BY column1

maybe not the most elegant way to do it,but it returns wat i want;)

any suggestions for improvements are welcomed:)

thanks!!:D

ChiragNS One Orange Chip Points: 26137 More actions · Answer 11

For starters...

specify the column names instead of "*".

SELECT column1 ,COUNT(column1) AS expr1

FROM (SELECT column1 FROM table1 WHERE column2='a')

GROUP BY column1

wont this suffice..

SELECT column1 ,COUNT(column1) AS expr1 from table1

where column2 = 'a'

GROUP BY column1

"Keep Trying"

toniupstny Hall of Fame Points: 3986 More actions · Answer 12

Would this be too simple an approach? I think it gets what you are after via Rowcount and could be gotten right after your initial query was done. Sometimes I simplify too much but let me know what you think.

select * from customers

where CustomerID Like 'A%'

select 'Rows are ', @@rowcount

Thank you

Toni

ChiragNS One Orange Chip Points: 26137 More actions · Answer 13

ChiragNS

One Orange Chip

Points: 26137

April 30, 2008 at 2:46 am

#808863

as i said dont use * if u can.

"Keep Trying"

toniupstny Hall of Fame Points: 3986 More actions · Answer 14

Agreed Chirag, you would not use the '*' (did not mean to imply you would - just lazy on my part to leave it in).

shwetha004 had stated "i managed to get a result using the above suggestions,but i am not sure hw to interpret it.first of all, wat i want is,to find out how many times a certain value appears in the result set of a query. " In the case he showed:

.for example, i have a table,table1, with 3 columns(column1,column2,column3).my query is:

Select * from table1 where column1='val'.

You would replace the '*' with column1 - columnwhatever: Select column1 from table1 where column1=val.

My point was if all you wanted was a way to count how many rows were in the result set of that query, @@ROWCOUNT would easily give it without needing to do anymore queries.

Toni