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.

  • 1
    Please 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, 2015 at 7:59

1 Answer 1


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.