CODEWITHSUNDEEP
sqlpostgresql
user7210645
user7210645

Reputation:

Postgresql count of occurrences in multiple columns

I have got a table with several visits of patients to several doctors, and each visit has got its own note column, for a staff member to insert notes from the visit.

Each visit has got a column for each Note from the visit, made by a staff member. e.g. Note_visit1, Note_visit2, Note_visit3. Each visit has also got a record of the staff's ID e.g. StaffID_visit1, StaffID_visit2, StaffID_visit3.

My problem is trying to answer this question:

The number of notes made by each staff member on each patient.

The table looks something like this, ignore the dumb notes. 

patientID|  Note_V1  |  StaffID_v1  |  Note_V2  |  StaffID_v2  |    
1        |  "cat"    |  101         |  "dog"    |  102         |  
2        |  "camel"  |  105         |  "rat"    |  101         |

Upvotes: 2

Views: 890

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I think you want something like this:

select patient_id, staffid, count(*)
from ((select patient_id, staffid_v1 as staffid, note_v1 as note
       from visits
      ) union all
      (select patient_id, staffid_v2 as staffid, note_v2 as note
       from visits
      ) union all
      (select patient_id, staffid_v3 as staffid, note_v3 as note
       from visits
      )
     ) v
where note <> ''
group by patient_id, staffid;

Upvotes: 1

Related Questions