Converting multiple rows through cast function in SQL

I have a database table with name, address, and monthly account expense as column information for every single month.

database table

name

address

monthly account expense

I want to add expenses from month1 to month12 data of an accounts table but it is in varchar data type, I want to convert it into float data type and also use a logical expression in the query that gets the SUM of all the expenses (month 1 + month 2 +...month12) should be greater than 10,000.

month1

month12

accounts

varchar

float

SUM

month 1

month 2

month12

10,000

But I am not able to solve this problem as the query is not working and am getting errors

Kindly check this query and let me know of any changes

Some pointers will be great to work around this problem.

2 Answers
2

The way you added and converted the month columns was incorrect.

month

Also you won't be able to call the alias of [Total Expense] in the HAVING clause on the same query where you declared it. Use a SUBQUERY.

[Total Expense]

HAVING

SUBQUERY

See below.

You have to Convert Each varchar column to Float Before Adding it.
Have a look for Explaining

, (CAST( [month1] as float) + CAST([month2] as float)... CAST([month12] as Float)) AS
'total expense'


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.