Postgresql count of occurrences in multiple columns

Question

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         |

Note that this is why you normalize tables; it makes queries such as this much easier. The table design is bad — unconditionally bad — not least because such queries are hard. Also, you seem to be missing crucial data, like date and time of each visit. Oh well, ... — Jonathan Leffler
– Jonathan Leffler, Commented Nov 25, 2016 at 17:56
Im not missing those fields I just chose not to include it here as it would just take up too much space in the post. Do you have any suggestions to improve the table? — user7210645
– user7210645, Commented Nov 25, 2016 at 18:01
Table PatientVisit: columns PatientID, StaffID, Note, (Date/Time,)... You might decide you want a VisitID column too, which might be an auto-allocated increasing number (sequence, ...). Then your query is essentially trivial. You also don't run into problems with someone who visits 70 times not having enough space for all their visits, and all sorts of other goodies. If you have 3 visits in a single row, you have to write each condition out 3 times — and worry about whether nulls screw things up when only the visit 1 columns hold data. — Jonathan Leffler
– Jonathan Leffler, Commented Nov 25, 2016 at 18:06
So what you're saying is that i should enter each new visit in the table as a row with a unique VisitID, and not new columns then. That makes more sense yea. @JonathanLeffler — user7210645
– user7210645, Commented Nov 25, 2016 at 18:35
Yes, each visit shoudl be a separate record in the PatientVisit table. Have you covered normalization yet? What you've got is an 'array' (of fixed size) in the columns of a table — that doesn't even count as 2NF. — Jonathan Leffler
– Jonathan Leffler, Commented Nov 25, 2016 at 18:37

Gordon Linoff · Accepted Answer · 2016-11-25 17:20:47Z

1

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;

answered Nov 25, 2016 at 17:20

Gordon Linoff

1.3m62 gold badges704 silver badges856 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql count of occurrences in multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related