In general, I have data on user costs, where users are in the rows, and in the columns of the month.

enter image description here

I need to add up both in rows and columns to get the total amount.
I use this query: (SUM(CAST(Replace([Колонка 1], '$','') as FLOAT))+SUM(CAST(Replace([Колонка 2], '$','') , but adding the third column is NULL. The data is in text form ($ 100.00), so I replace them with a numeric one.

What could be the problem when calculating the entire amount? Prompt, please, the correct request.

  • Do coalesce(sum(...),0)+coalesce... - Mike
  • super! worked) - Carol

1 answer 1

Try creating a VIEW :

 CREATE VIEW sum_per_month AS SELECT , user_id , CAST(Replace([Колонка 1], '$','') as FLOAT) AS [1 month later] , CAST(Replace([Колонка 2], '$','') as FLOAT) AS [2 month later] , CAST(Replace([Колонка 3], '$','') as FLOAT) AS [3 month later] , [1 month later] + [2 month later] + [3 month later] AS id_sum FROM source_table;

And then, make a request to him:

 SELECT SUM(id_sum) FROM sum_per_month;