0

Here is my line of query where I am getting error msg 8114, level 16, state 5. I have the sum and rounding exactly as I need it - just can't get the + '%' part to work: 

SUM(CAST(ROUND(ENROLLMENT,1) AS DECIMAL (10,2)))/SUM(CAST(ROUND(CAPACITY,1) AS DECIMAL (10,2))) *100 + '%' AS 'fill rate %'

Any help will be appreciated!

Improve this question
1
  • your mixing the concepts of the data format and display with that of the data type. you can't combine STRING data on a NUMERIC data type unless your final result is string/character. You'd have to cast the decimal value as string to add the % or leave off the % as the fill rate % column header already indicates it's a %. Commented Feb 22, 2017 at 18:55

1 Answer 1

Reset to default
1

Either cast your decimal to varchar

cast(SUM(CAST(ROUND(ENROLLMENT,1) AS DECIMAL (10,2)))/SUM(CAST(ROUND(CAPACITY,1) AS DECIMAL (10,2))) *100 as varchar(100)) + '%' AS 'fill rate %'

or simply don't add the %. The column header already say's it's a % field...

cast(SUM(CAST(ROUND(ENROLLMENT,1) AS DECIMAL (10,2)))/SUM(CAST(ROUND(CAPACITY,1) AS DECIMAL (10,2))) *100 as varchar(100)) AS 'fill rate %'

Or wherever you're displaying this have the display add the '%'to the right of the field displaying the decimal value.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

You are a life safer! I had tried the varchar but didn't have the CAST at the very beginning. Thank you so much, I really appreciate it! I'm self-taught and I know I have a lot more learning to do!
If this is a field which the user can update, if you add the %, you'll have to strip it off when they save.
Is it possible to contact you directly? If so, I have another query I'm just too embarrassed to post for help!
That's not what the site does. Post a new question people help. Get over the fact it may be embarrassing, we all had to start somewhere and learn from it. Just make sure to do your research up front, show some effort. If you do those things and still can't figure it out, then ask and show what you've tried and why. If you can't laugh at yourself and the mistakes you'll make, it's going to be a long career. Embrace the mistakes and learn from them and you'll do well.
I'm so sorry for asking!

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.