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.
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.