Help with Multiple Row SUM in SQL 2008 R2

[MimUK]

I have a Microsoft SQL Server 2008 R2 table as follows..

CREATE TABLE [dbo].[CRSpendByPeriod](
[crmcref] [char](6) NOT NULL,
[year] [numeric](5, 0) NOT NULL,
[mtdvalue_1] [numeric](10, 2) NULL,
[mtdvalue_2] [numeric](10, 2) NULL,
[mtdvalue_3] [numeric](10, 2) NULL,
[mtdvalue_4] [numeric](10, 2) NULL,
[mtdvalue_5] [numeric](10, 2) NULL,
[mtdvalue_6] [numeric](10, 2) NULL,
[mtdvalue_7] [numeric](10, 2) NULL,
[mtdvalue_8] [numeric](10, 2) NULL,
[mtdvalue_9] [numeric](10, 2) NULL,
[mtdvalue_10] [numeric](10, 2) NULL,
[mtdvalue_11] [numeric](10, 2) NULL,
[mtdvalue_12] [numeric](10, 2) NULL,
[ytdvalue] [numeric](10, 2) NULL,) 
ON [PRIMARY]
GO

If I select where year = 2013 and 2014 it returns 2 rows per crmcref (Account Number)

I need to sum mtdvalue_5, mtdvalue_6, mtdvalue_7, mtdvalue_8, mtdvalue_9, mtdvalue_10, mtdvalue_11, mtdvalue_12 where year is 2013 and then add mtdvalue_1, mtdvalue_2, mtdvalue_3, mtdvalue_4 where year is 2014

In essence the data is split into Financial Years and I need to work out the math based on Past 12 months (mtdvalue_1) being May and year being financial year (2014 being current year).

I would really appreciate some help.
Its worth noting I have no control of the database tables and structure (normalization), but I can create views.

Mim

 

[gmmastros]

If I understand correctly, this may be what you are looking for.

Select crmcref,
       Sum(Case When  year = 2013 
                Then  Coalesce(mtdvalue_5, 0) 
                      + Coalesce(mtdvalue_6, 0) 
                      + Coalesce(mtdvalue_7, 0) 
                      + Coalesce(mtdvalue_8, 0) 
                      + Coalesce(mtdvalue_9, 0) 
                      + Coalesce(mtdvalue_10, 0) 
                      + Coalesce(mtdvalue_11, 0) 
                      + Coalesce(mtdvalue_12, 0) 
                When  year = 2014 
                then  Coalesce(mtdvalue_1, 0)  
                      + Coalesce(mtdvalue_2, 0)  
                      + Coalesce(mtdvalue_3, 0)  
                      + Coalesce(mtdvalue_4, 0)
                Else 0 End) As mdt_total
From   CRSpendByPeriod
Group By crmcref

There are a couple things I should explain about this.

When you use SUM, null values are ignored. In this code, you should use the coalesce function because we are adding values from multiple columns together. If one of the columns contains NULL, then the addition would return null, and you would end up missing some values. By using Coalesce(Column, 0), we can be assured that NULL values are treated as 0 for the purpose of the addition and therefore the subsequent SUM.

Also notice that I used a CASE/WHEN expression. This allows us to use different columns depending on the value in another column.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MimUK]

Thank you gmmastros. Much appreciated