Count multiple columns 1

[scriptscribe]

Hi,

I have a table that looks like this:

AdmitDate AcctNum
2005-03-04 5713513
2005-05-11 5713683
2005-05-18 5714069
2005-06-01 5714138
2005-06-03 5714152
2005-06-05 5714153
2005-06-05 5711825

I want to be able to count by both distinct AcctNum AND AdmitDate, because there will be some duplicate AcctNums for the same AdmitDate and some duplicate AcctNums with different AdmitDates. So in other words, I want a count of each unique AcctNum for each AdmitDate.

I tried this:

select count(DISTINCT(AcctNum, AdmitDate)) from KnownMedicaid

...but got an error message saying that COUNT can't be used with more than one column.

Any help would be greatly appreciated.

Thanks,
Mike

 

[ptheriault]

Did you try this.

select DISTINCT count(AcctNum) 
from KnownMedicaid
GROUP BY AdmitDate

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[scriptscribe]

That gave me this:

1
2
3
5
6
...

I just need one total count.

 

[gmmastros]

one way....

Select Count(*)
From   (
       select DISTINCT AcctNum, AdmitDate 
       from KnownMedicaid
       ) as A

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

select count(*) from
(select AcctNum, AdmitDate
from KnownMedicaid
group by AcctNum, AdmitDate) z


example

create table bla (col1 int,col2 int)

insert bla values(1,1)
insert bla values(1,1)
insert bla values(2,2)
insert bla values(3,1)

select count(*) from(
select * from bla
group by col1,col2) z

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[ptheriault]

Sorry about that.

I miss understood when you said. " I want a count of each unique AcctNum for each AdmitDate."

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[scriptscribe]

Thanks, George. That works. Star for you.