How To Take Count Of Id's - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How To Take Count Of Id's

Author

Topic

taj
Starting Member

39 Posts

Posted - 2013-09-07 : 02:02:13

Hi All,

I have a table Patient_Lab_Tests as shown below:

PATIENT_ID TEST_CODE REPORT_GENERATED
101 6 Y
101 12 Y
101 15 N
102 6 Y
102 12 Y
102 15 Y
103 6 Y
103 12 Y

REQUIREMENT:

I want to show the count of Patient_id 's for which the Report_Generated column is 'Y'

Output Result:
Patient_Count
2

My Result is only 2 because for PATIENT_ID's 102 & 103 the REPORT_GENERATED Columns is 'Y' for all tests.So PATIENT_ID 101 is not consider in my count since one of the test_code for patient_id 101 is 'N' in REPORT_GENERATED column.

How to do this using sql server?

Thank You,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-09-07 : 03:14:09

[code]select count(*)
from
(
select PATIENT_ID
from yourtable
group by PATIENT_ID
having min(REPORT_GENERATED) = 'Y'
) d[/code]

KH
[spoiler]Time is always against us[/spoiler]

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-07 : 03:24:02

Alternative:


with patient_lab_tests (patient_id,test_code,report_generated)
  as (          select 101,6,'Y'
      union all select 101,12,'Y'
      union all select 101,15,'N'
      union all select 102,6,'Y'
      union all select 102,12,'Y'
      union all select 102,15,'Y'
      union all select 103,6,'Y'
      union all select 103,12,'Y'
     )
select count(*) as patient_count
  from (select patient_id
          from patient_lab_tests
         group by patient_id
         having avg(case when report_generated='Y' then 1 else 0 end)=1
       ) as a

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-09-08 : 04:33:18

[code]select count(*)
from
(
select PATIENT_ID
from yourtable
group by PATIENT_ID
having min(REPORT_GENERATED) = 'Y' and max(REPORT_GENERATED) = 'Y'
) d
[/code]
Also refer this to know how you can use HAVING clause for various purposes http://beyondrelational.com/modules/2/blogs/70/posts/19573/efficient-filtering-with-having-clause.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources