Distinct Count in SQL with conditions

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
jul_to_coolAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

savic7ukCommented:
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
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jul_to_coolAuthor Commented:
Thanks!  That's what I needed!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.