CODEWITHSUNDEEP
sqlsql-servert-sqlcountgroup-by
Gyuzal
Gyuzal

Reputation: 1591

How to group values of one column based on condition in t-sql?

My table has doctor ID, visit ID (one doctor can have several visits) and visit cause, it looks like this:

doctor_uid | visit_uid | visit_cause
11-11-11    22-22-11      1
22-22-22    44-44-22      2
11-11-11    23-23-23      1

I need to count all visits for each doctor, based on visit_cause (it should be equal '1'). Before I count all visits for each doctor without condition of visit_cause, I must have this in my query too:

SELECT 
   v.vra_uid, 
   COUNT(v.visit_uid) OVER (PARTITION BY v.vra_uid) AS number_of_visits
FROM visits v

It returns:

doctor_uid | number_of_visits 
    11-11-11    2     
    22-22-22    1

How can I count those visits, that have visit_cause = '1' for each doctor? so that it would look like:

    doctor_uid | number_of_visits | visits_with_cause_equal_1
    11-22-33    2                           2
    22-22-22    1                           0

Thanks in advance!

Upvotes: 1

Views: 2437

Answers (2)

gzaxx
gzaxx

Reputation: 17590

This should work:

select v.vra_uid, COUNT(v.visit_uid) as number_of_visits , SUM(case when v.visit_cause = 1 then 1 else 0 end) as visits_with_cause_equal_1
from visits v 
group by v.vra_uid

Upvotes: 3

DoStuffZ
DoStuffZ

Reputation: 800

have you tried CASE WHEN visit_cause = '1' THEN COUNT(*)

Upvotes: 0

Related Questions