Distinct Count in SQL with conditions

jul_to_cool
jul_to_cool used Ask the Experts™
on
Hi All,

I need to identify the PtID that have 2+ unique ORDER_MED_ID'S and the DISCON_TIME is null, I'll need to display the number of unique ORDER_MED_ID's that meet the condition and then limit the query to people that have 2+ unique ORDER_MED_ID'S that meet the condition.   How can I accomplish this?

select
orm.ORDER_MED_ID
, orm.DISCON_TIME

from PAT_ENC pae
inner join ORDER_MED orm ON pae.PAT_ENC_CSN_ID=orm.PAT_ENC_CSN_ID

where
#ofmeds >=2

From the example below, PtID 1234 and 3456 meet the condition.

The results look like this:
PtID                    ORDER_MED_ID               DISCON_TIME

1234                    0101233                          
1234                    0101234                          
1234                    0101235                          
1234                    0101236
2345                    0101237
3456                    0101238
3456                    0101239
3456                    0101240                           2015-07-20
4567                    0101241                           2015-07-20
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hi,

It looks that ORDER_MED_ID is unique (correct?)
ThenTry that,

select   PtID , count(*) ,
from PAT_ENC pae
inner join ORDER_MED orm ON pae.PAT_ENC_CSN_ID=orm.PAT_ENC_CSN_ID
where
DISCON_TIME is null
Group by PtID
Having count(*)>=2

Author

Commented:
Thanks!  That's what I needed!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial