view raw
Richard Richard - 1 year ago 42
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
, with a recommended pills per day of 4, and one with code
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:

Now I need to work out how many daily doses were prescribed for all codes starting
. 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
for all codes starting
? There might be several hundred, but I'd prefer to use a single SQL query if possible.


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.