count from distinct

[anselme]

Hello

for mS SQL 2000

with

SELECT DISTINCT Name, Region
FROM Groups
GROUP BY Name, Region

I get 254 rows

but how can I get the COUNT only ?

something like

SELECT COUNT(SELECT DISTINCT Name, Region
FROM Groups
GROUP BY Name, Region) AS CPT FROM Groups

i must get 254

thank you for helpings

[rmiao]

You can get it with @@rowcount like:

SELECT DISTINCT Name, Region
FROM Groups
GROUP BY Name, Region

select @@rowcount

[anselme]

never heard before !

thank you

[skhanal]

If you don't care about rows returned and interested in count only then

select count(*)
from (SELECT DISTINCT Name, Region
FROM Groups
GROUP BY Name, Region
) as A

[nosepicker]

Just an FYI: you don't need to do a GROUP BY if you are using DISTINCT. Also, you can get the count of distinct records this way, although it isn't necessarily faster:

SELECT COUNT(DISTINCT name + region) FROM Groups

(this only works if the columns are char or varchar - if not, you have to convert them to one of those datatypes)