GROUP BY, GROUP BY, and DISTINCT

I have an SQL-Server table that contains "date, name, and account number"
records like:
22-Jan-2005 Bill BA39833J
22-Jan-2005 Bill RJ3399K
22-Jan-2005 Bill KL9833LL
22-Jan-2005 Bill BA39833J
23-Jan-2005 Bill HP54599K
23-Jan-2005 Bill AA9833LL
23-Jan-2005 Bill BA90330Q
24-Jan-2005 Bill BA8993PPQ
24-Jan-2005 Bill BA8993PPQ
24-Jan-2005 Bill XX93939
24-Jan-2005 Bill BA8993PPQ
24-Jan-2005 Bill BA8993PPQ
I need to "group by" the date, and the user... and then "count()" the total
number
of account numbers that appear for each record.
... but here's the odd part...
I need to count each account # as "1"... except when there are similar
account numbers... then those all count as "1".
(Not counting each as "each".)
So the result will look something like:
22-Jan-2005 Bill 3 (Not 4, because the 2 similar values count as
1)
23-Jan-2005 Bill 3
24-Jan-2005 Bill 2 (Not 5, because the 4 similar values count as
1)
I thought all I would have to do is to "group by" all 3 fields... "date,
user, and account
number"... but that, of course, gives me something that I do NOT want.
ThanksTry,
select
colA,
colB,
count(distinct colC)
from
t
group by
colA,
colB
order by
colA,
colB
AMB
""A_Michigan_User"" wrote:

> I have an SQL-Server table that contains "date, name, and account number"
> records like:
> 22-Jan-2005 Bill BA39833J
> 22-Jan-2005 Bill RJ3399K
> 22-Jan-2005 Bill KL9833LL
> 22-Jan-2005 Bill BA39833J
> 23-Jan-2005 Bill HP54599K
> 23-Jan-2005 Bill AA9833LL
> 23-Jan-2005 Bill BA90330Q
> 24-Jan-2005 Bill BA8993PPQ
> 24-Jan-2005 Bill BA8993PPQ
> 24-Jan-2005 Bill XX93939
> 24-Jan-2005 Bill BA8993PPQ
> 24-Jan-2005 Bill BA8993PPQ
> I need to "group by" the date, and the user... and then "count()" the tota
l
> number
> of account numbers that appear for each record.
> ... but here's the odd part...
> I need to count each account # as "1"... except when there are similar
> account numbers... then those all count as "1".
> (Not counting each as "each".)
> So the result will look something like:
> 22-Jan-2005 Bill 3 (Not 4, because the 2 similar values count
as
> 1)
> 23-Jan-2005 Bill 3
> 24-Jan-2005 Bill 2 (Not 5, because the 4 similar values count
as
> 1)
> I thought all I would have to do is to "group by" all 3 fields... "date,
> user, and account
> number"... but that, of course, gives me something that I do NOT want.
> Thanks
>
>