Converting multiple rows through cast function in SQL

Question

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

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.

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

SELECT name
      ,address
      ,CAST(([month1] + [month2]...[month12] AS float) AS 'total expense'
FROM Accounts_Table
WHERE name LIKE 'a%'
GROUP BY name, address, 'total expense'
HAVING 'total expense' > 10000

Some pointers will be great to work around this problem.

Answer 1

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

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.

See below.

SELECT [name]
      ,[address]
      ,[Total Expense]
FROM (
    SELECT [name]
          ,[address]
          ,CAST([month1] AS float) +
           CAST([month2] AS float) +
           CAST([month3] AS float) +
           CAST([month4] AS float) +
           CAST([month5] AS float) +
           CAST([month6] AS float) +
           CAST([month7] AS float) +
           CAST([month8] AS float) +
           CAST([month9] AS float) +
           CAST([month10] AS float) +
           CAST([month11] AS float) +
           CAST([month12] AS float) AS [Total Expense]
    FROM [Accounts_Table]
    WHERE [name] LIKE 'a%') AS [src]
GROUP BY [name]
        ,[address]
        ,[Total Expense]
HAVING [Total Expense] > 10000