October 14, 2005 at 5:24 pm
Does this: SELECT region, C2.classID, count(distinct email)as signups
Mean the same thing as:
SELECT count(distinct email)as signups , region, C2.classID
In other words does distinct apply to everything, or only to what follows it ?
October 15, 2005 at 11:06 pm
Count(distinct email) only counts the distinct emails from your resultset.
if you do
select distinct region, c2.classID, count(*)
From MyTable
Group by region, c2.classID
But I'm not exactly sure, post some data if you need additional help w/ your query.
Lookup Count() in Books online.
October 16, 2005 at 1:41 pm
The order of the items in the select list doesn't matter at all. DISTINCT applies to the row, not to the column when you do this (the statments will return the same information):
select Distinct a,b,c from myTable
select Distinct c,b,a from myTable
But, in your example, you are using Distinct inside of a function, so it only applies to the email column inside the function. Also, since you are using count(), an aggregate, the count is determined on each grouping, not over the entire resultset.
Here is an example to illustrate:
create table distinct_test (a int null, b int null, c int null)
go
insert into distinct_test select 1,1,1
insert into distinct_test select 1,1,2
insert into distinct_test select 1,1,3
insert into distinct_test select 1,2,1
select a, count(distinct b)as count_of_distinct_b, c
from distinct_test
group by a, c
a count_of_distinct_b c
--- ------------------- ---
1 2 1
1 1 2
1 1 3
The first and last row are grouped together and there are two distinct b values found in that group
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply