CODEWITHSUNDEEP
sql-serversumrow
John
John

Reputation: 1061

How to sum the data in a row in SQL Server?

I have a table with sample like this:

enter image description here

In this example there are Day1, Day2, and Day3 but in the real data it could be Day1 through Day100.

How can I sum the data in the row for a particular range like Day1 through Day3 or Day2 through Day10 for TSTID = 1?

Upvotes: 1

Views: 120

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82020

This approach is a little more dynamic, without actually being dynamic. Notice that we don't have to specify or dynamically create the field list.

Declare @YourTable table (TSTID int,Year int,Day1 int,Day2 int,Day3 int)
Insert Into @YourTable values
(1,2015,1000,4000,7000),
(2,2015,2000,5000,8000),
(3,2015,3000,6000,9000)

Declare @DayR1 int = 1
Declare @DayR2 int = 3

Select A.TSTID
      ,TotalSum = sum(B.Value)
 From @YourTable A
 Cross Apply (Select Value=cast(value as int) 
              From   [dbo].[udf-EAV]((Select A.* for XML RAW)) 
              Where  Attribute Like 'Day%' 
                and  cast(Replace(Attribute,'Day','') as int) between 1 and 3
              )  B 
 Where A.TSTID = 1
 Group By A.TSTID

Returns

TSTID   TotalSum
1       12000

Now, I do use a helper function here. It is a TVF which converts virtually any dataset into an EAV structure (Entity Attribute Value). CLEARLY UNPIVOT would perform better with LARGER datasets.

I should add, that if you don't want the function, the logic can easily be ported into the CROSS APPLY

CREATE FUNCTION [dbo].[udf-EAV](@XML xml)
Returns Table 
As
Return (
    with cteKey(k) as (Select Top 1 xAtt.value('local-name(.)','varchar(100)') From @XML.nodes('/row') As A(xRow) Cross Apply A.xRow.nodes('./@*') As B(xAtt))    

    Select Entity    = xRow.value('@*[1]','varchar(50)')
          ,Attribute = xAtt.value('local-name(.)','varchar(100)')
          ,Value     = xAtt.value('.','varchar(max)') 
    From  @XML.nodes('/row') As A(xRow)
    Cross Apply A.xRow.nodes('./@*') As B(xAtt)
    Where xAtt.value('local-name(.)','varchar(100)') Not In (Select k From cteKey)
)
-- Notes:  First Field in Query will be the Entity
-- Select * From [dbo].[udf-EAV]((Select UTCDate=GetUTCDate(),* From sys.dm_os_sys_info for XML RAW))

Upvotes: 1

Related Questions