March 17, 2009 at 9:14 pm
Hi frendz
I have got a table of patients which I need to aggregate in two different ways:
Here is the sample data:
Doctor_Code Patient_code TotalTests
A1 12121 56
A1 0000 12
B1 111 5
B1 0000 111
B1 4533 11
...
..
...
In this table one doctor has many patients each having a different code.Those patients with no codes have been defaulted to four zeros(0000).
NOw i need to count the total tests by each doctor for patients with valid codes and with '0000' codes.
The result shd be something like this:
Doctor_code Total_Valid_Test Total_Invalid_Tests
A1 56 12
.
.
and so on..
How do I write the sql query for this??
Thanks
March 17, 2009 at 9:34 pm
You need to combine GROUP BY and a CASE statement:
select doctor_code, sum(case when patient_code = '0000' then totaltests else 0 end)
from YOUR_TABLE
group by doctor_code
You can add more columns with other criteria.
Bevan
March 17, 2009 at 9:45 pm
Hi,
try this statements
declare @HOS table (
Doctor_Code char(5),
Patient_code char(10),
TotalTests int)
--
insert into @HOS values ('A1','12121',56)
insert into @HOS values ('A1','0000',12)
insert into @HOS values ('B1','111',5)
insert into @HOS values ('B1','0000',111)
insert into @HOS values ('B1','4533',11)
--
--select * from @HOS
--
select a.Doctor_Code,
(
select count( b.Doctor_Code)
from @HOS as b
where b.Doctor_Code = a.Doctor_Code
and b.Patient_code <> '0000'
)Total_Valid_Test,
(
select count( c.Doctor_Code)
from @HOS as c
where c.Doctor_Code = a.Doctor_Code
and c.Patient_code = '0000'
)Total_Invalid_Tests
from @HOS as a
group by a.Doctor_Code
RESULT
Doctor_CodeTotal_Valid_TestTotal_Invalid_Tests
A1 1 1
B1 2 1
ARUN SAS
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply