Richard - 1 year ago 63

SQL Question

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`

`04030202`

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`

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`

`0403`

Answer Source

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.