calculate Row Wise Sum - Sql server

Question

This is My table :

ID              Q1         Q2           Q3            Q4
----------------------------------------------------------------
20130712        NULL       728.63       NULL            NULL
20130712        8881.55    9673.68      2629.566        6251.984
20130713        1813       1813         84.49           1728.51
20130714        3632.65    3632.65      1209.412        2423.238
20130714        70.758     2637.43      70.758          0
20130714        1601.578   3569.73      204.745         1396.833
20130714        728.63     728.63       0               728.63
20130714        1401.629   2251.39      94.418          1307.211
20130715        583.956    5089.19      583.956         0
20130805        6317.277   8958         2629.566        3687.711

I want the output like below.(The columns might change dynamically, needs to calculate Sum by row wise)

ID              Q1         Q2           Q3            Q4             SUM(Q1:Q4)
---------------------------------------------------------------------------
20130712        NULL       728.63       NULL            NULL         728.63   
20130712        8881.55    9673.68      2629.566        6251.984     27436.78
20130713        1813       1813         84.49           1728.51      5439
20130714        3632.65    3632.65      1209.412        2423.238     ...
20130714        70.758     2637.43      70.758          0
20130714        1601.578   3569.73      204.745         1396.833
20130714        728.63     728.63       0               728.63
20130714        1401.629   2251.39      94.418          1307.211
20130715        583.956    5089.19      583.956         0
20130805        6317.277   8958         2629.566        3687.711

The output is just the original table without the `Total` column. Did you get them backwards? — Barmar, Aug 15 '13 at 04:08

score 22 · Answer 1 · answered Aug 15 '13 at 04:23

22

You haven't shown your query attempt, but it's probably something like this:

SELECT
  ID, Q1, Q2, Q3, Q4,
  Q1 + Q2 + Q3 + Q4 AS "Total"
FROM MyTable

If any of the Q1, Q2, Q3, or Q4 values are null, Q1 + Q2 + Q3 + Q4 will be null. To treat the nulls as zero and get a proper sum, do this instead:

SELECT
  ID, Q1, Q2, Q3, Q4,
  COALESCE(Q1,0) + COALESCE(Q2,0) + COALESCE(Q3,0) + COALESCE(Q4,0) AS "Total"
FROM MyTable

The COALESCE function will return the first non-null value in the list.

answered Aug 15 '13 at 04:23

Ed Gibbs

25,924
4
46
69

Yet another option is to use NVL function, e.g: NVL(Q1, 0) which returns Q1 if Q1 is not NULL and 0 if Q1 is NULL – Dmitry Bychenko Aug 15 '13 at 05:54
@DmitryBychenko - don't think SQL Server has `NVL`. And `COALESCE` is standard/portable. – Damien_The_Unbeliever Aug 15 '13 at 06:21
Needs some assistant: The columns in a result set may be increase or decrease then if we hard core column names, the query may not give correct results. So can please help me for dynamic query. – Uday Aug 15 '13 at 06:51
@user1613094 - there are two ways to do a dynamic query: (1) use your front-end language to assemble and run the query or (2) write a SQL Server procedure that uses `EXEC` or `sp_executesql`. I recommend posting a new question specifically about dynamic SQL, and when you post the new question please (a) include some working SQL and (b) be careful to get the table and output right - I'm pretty sure you had them switched for this question, and it's hard to get help if a question is confusing. – Ed Gibbs Aug 15 '13 at 13:34
works flawlessly @EdGibbs – Gaurav Nov 11 '21 at 08:20

Roman Pekar · Answer 2 · 2013-08-15T06:58:37.290

Don't know if it there's a shorter way, but the most elegant I can do is:

select
    ID, Q1, Q2, Q3, Q4,
    (
         select sum(S.Q)
         from (values (Q1), (Q2), (Q3), (Q4)) as S(Q)
         where S.Q is not null
    ) as [Total]
from Table1 as T

sql fiddle demo

If you want dynamic SQL, try something like

declare @stmt nvarchar(max), @stmt1 nvarchar(max)

select
    @stmt = isnull(@stmt + ', ', '') + name,
    @stmt1 = isnull(@stmt1 + ', ', '') + '(' + name + ')'
from sys.columns
where object_id = object_id('Table1') and name not in ('ID')

select @stmt =
          'select ID, ' + @stmt + 
          ', (select sum(S.Q) from (values ' + @stmt1 +
          ') as S(Q) where S.Q is not null) as [Total] ' +
          'from Table1 as T'

exec sp_executesql @stmt = @stmt

sql fiddle demo

Needs some assistant: The columns in a result set may be increase or decrease then if we hard core column names, the query may not give correct results. So can please help me for dynamic query. — Uday, Aug 15 '13 at 06:51

score 1 · Answer 3 · answered Nov 01 '17 at 19:33

Expanding on Roman Pekar, If you're using a temp table and want to do this, you need to use tempdb like this:

select
@stmt = isnull(@stmt + ', ', '') + '[' + name + ']',
@stmt1 = isnull(@stmt1 + ', ', '') + '(' + '[' + name + ']'+  ')'
from tempdb.sys.columns
where object_id = object_id('tempdb..##TempTable') and name not in ('ID')
--ID would be one of the column names you DONT want to sum.
--also notice the double pound sign. you need to declare your temp table with double pounds or it wont work
--also notice how I put brackets around name, that's because my columns weren't working because they had slashes in their names.
--the rest of the code is the same
select @stmt =
          'select Date_Packed, ' + @stmt + '' + 
          ', (select sum(S.Q) from (values ' + @stmt1 +
          ') as S(Q) where S.Q is not null) as [Total] ' +
          'from tempdb..##TempTableas T'
          print @stmt

exec sp_executesql @stmt = @stmt
--don't forget to drop it
          drop table ##TempTable

calculate Row Wise Sum - Sql server

3 Answers3

Linked

Related