How can I sum the full dose of a medication in the dose_val_rx column in the prescription table? I had tried to sum(dose_val_rx) nbut it does not work.
-
1Please could you state which database system you are using (Oracle, Postgres, MySQL etc) as well as the specific error message that you receive? – tomp Dec 23 '15 at 7:59
dose_val_rx is a varchar column that contains non-numerical values (for example, sometimes a range is specified such as '1-2' tablets). It isn't clear how you would sum these values, so most database systems will return a type error. Postgres, for example, returns:
HINT: No function matches the given name and argument types.
You might need to add explicit type casts.
To sum the values you would first need to cast them to a number. If your medications of interest are measured numerically then this should be straightforward. Otherwise you will have to define rules for dealing with cases such as '1-2'. Syntax for casting varies between database systems.
