Adding text to this line

Question

I'm a novice so please excuse the simplicity of this. I am trying to get a total ammount, trim the excess decimal places than append some text.. it all works until I try ROUND it

ROUND(CAST(ServiceFee * COUNT(UserID) AS VARCHAR(20)),0) + CAST(' yen' as VARCHAR(20))

Thanks in advance

Answer 1

You have to round the value before you cast it:

CAST(ROUND(ServiceFee * COUNT(UserID),0) AS VARCHAR(20)) + ' yen'

Answer 2

Depends on the dialect of SQL.

For example, in MySQL the + operator is for mathematical addition only. If you want to concatenate the values, should use CONCAT() (in MySQL) or the || operator (other DBMSes that support standard SQL). You're also doing a redundant CAST within ROUND() because the ROUND function expects its argument to be numeric.

So here's the fixed statement in MySQL:

CONCAT(ROUND(ServiceFee * COUNT(UserID), 0), ' yen')

Or in standard SQL:

CAST(ROUND(ServiceFee * COUNT(UserID), 0) AS VARCHAR(20)) || ' yen'

(the CAST is probably redundant, but I kept it just in case you had a purpose for it)