Richard Richard - 7 months ago 20
SQL Question

Conditional aggregation with JOIN?

I am working in Postgres 9.4. I have a table containing medications, as follows:

bnf_code │ character varying(15) │ not null
pills_per_day │ double precision │


For example, this table might contain a medication with code of
04030201
, with a recommended pills per day of 4, and one with code
04030202
and recommended pills per day of 2.

And I also have a table containing numbers of prescriptions, with a foreign key to the table above:

code │ character varying(15) │ not null
num_pills │ double precision │ not null
processing_date │ date │ not null
practice_id │ character varying(6) │ not null
Foreign-key constraints:
FOREIGN KEY (code) REFERENCES medications(bnf_code) DEFERRABLE INITIALLY DEFERRED


Now I need to work out how many daily doses were prescribed for all codes starting
0403
. The daily dose is defined as the number of pills actually prescribed, divided by the recommended pills per day.

I know how to do this for the two particular codes above:

SELECT (SUM(num_pills) FILTER (WHERE code='04030201') / 4) +
(SUM(num_pills) FILTER (WHERE code='04030202') / 2)
FROM prescriptions


But that's because I can hard-code in the pills per day field.

Can I extend this to divide by the appropriate
pills_per_day
for all codes starting
0403
? There might be several hundred, but I'd prefer to use a single SQL query if possible.

Answer

I am not sure if this is what are you looking for:

SELECT SUM(p.num_pills/r.pills_per_day)
FROM prescriptions p INNER join recommendations r 
    ON p.code = r.bnf_code
WHERE p.code Like '0403%'

I am assuming that num_pills is the number of pills prescribed and pills_per_day is the number of pills recommended.