CODEWITHSUNDEEP
sql-servertype-conversionsql-server-2014sqldatatypessql-query-store
sxb1649
sxb1649

Reputation: 27

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.

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.

Upvotes: 0

Views: 1579

Answers (2)

Bhargav J Patel
Bhargav J Patel

Reputation: 166

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'

Upvotes: 0

CurseStacker
CurseStacker

Reputation: 1079

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

Upvotes: 1

Related Questions