0

I need to find the doctors revenue from various departments like laboratory,radiology,pharmacy and other departments trough patients.

I have only document_number column where the values recorded as 'L1432','R87j7','P652', etc. if doc_no starts with 'L' then it is laboratory, if doc_no starts with 'R' then it is radiology, if doc_no starts with 'P' then it is pharmacy. How can I do this in SQL?

Output should look like this:

doctor_name  laboratory   radiology   pharmacy    others 
Michel       23098        6763        78732       98838
John         77838        89898       56542       52654
Cranys       98973        78763       5432        65565
Abdur Rahmaan
  • 29
  • 4
  • Your data model is flawed. Putting logic into a key is not a really good idea. Your table should have a proper foreign key pointing to a `departments` table –  Oct 28 '16 at 06:18

1 Answers1

1

This is a conditional aggregation by the first character of the document_number:

select doctor_name,
       sum(turnover) filter (where left(document_number,1) = 'L') as laboratory, 
       sum(turnover) filter (where left(document_number,1) = 'R') as radiology, 
       sum(turnover) filter (where left(document_number,1) = 'P') as pharmacy, 
       sum(turnover) filter (where left(document_number,1) not in ('L','R','P')) as others
from the_table
group by doctor_name
order by doctor_name;