CODEWITHSUNDEEP
sqlsql-servert-sql
Deep Sharma
Deep Sharma

Reputation: 3473

SQL sum values from different columns based on condition

Following is the data that i have in a table:

Date           Original    Estimated     Actual
2013-04-14     141.44323    NULL          384.875
2013-04-14     31.184295    NULL          200.375
2013-04-14     0            NULL          54.75
2013-04-14     0            NULL          0.625
2013-04-15     5.4326204    NULL          0
2013-04-15     45.795869    NULL          -0.375
2013-04-15     86.57694     NULL          11.875
2013-04-15     186.219      NULL          58.875

i want to sum values of column Actual but if its value is zero or null then for that specific row value should be taken from Estimated column and if Estimated column also have zero value then value will be taken from Original column. and i want the result to be grouped by Month and year. Please help

Upvotes: 2

Views: 3150

Answers (4)

Shavkat
Shavkat

Reputation: 83

NullIf is the best solution as suggestd by Tomas Greif. Corrected code:

declare @data table (
    [Date] datetime not null, 
    [Original] decimal(18,9) null, 
    [Estimated] decimal(18,9) null, 
    [Actual] decimal(18,9) null);

insert into @data
    ([Date],           [Original],   [Estimated],    [Actual])
values 
    ('2013-04-14',     141.44323,    NULL,          384.875),
    ('2013-04-14',     31.184295,    NULL,          200.375),
    ('2013-04-14',     0,            NULL,          54.75),
    ('2013-04-14',     0,            NULL,          0.625),
    ('2013-04-15',     5.4326204,    NULL,          0),
    ('2013-04-15',     45.795869,    NULL,          -0.375),
    ('2013-04-15',     86.57694,     NULL,          11.875),
    ('2013-04-15',     186.219,      NULL,          58.875);

select 
    [DateMonth] = dateAdd(month, dateDiff(month, 0, [Date]), 0),
    [SumMonth]  = SUM(COALESCE(NULLIF(Actual,0),NULLIF(Estimated,0),Original))
from @data
group by dateAdd(month, dateDiff(month, 0, [Date]), 0)

Upvotes: 0

bjnr
bjnr

Reputation: 3437

SELECT
    SELECT DATEPART(YEAR, Date) as [Year],
    DATEPART(MONTH, Date) as [Month],
    SUM(COALESCE(NULLIF(Actual,0), NULLIF(Estimated, 0), Original)
FROM YourTable
GROUP BY DATEPART(YEAR, Date), DATEPART(MONTH, Date);

Upvotes: 2

Tomas Greif
Tomas Greif

Reputation: 22623

What about using nullif? This will be much shorted than CASE statement.

select 
   DATEPART(YEAR, Date),
   sum(coalesce(nullif(Actual,0),nullif(Estimate,0),Original))
from
   MyTable
group by
   DATEPART(YEAR, Date);

Upvotes: 0

StuartLC
StuartLC

Reputation: 107237

I believe you want COALESCE

SELECT DATEPART(YEAR, Date), DATEPART(MONTH, Date), SUM(Coalesce(Actual, Estimated, Original)) FROM MyTable GROUP BY DATEPART(YEAR, Date), DATEPART(MONTH, Date);

Edit As @MartinSmith pointed out, the NULL OR Zero will need to be catered for. This can be done by projecting zero back to NULL with a CTE before running it through the COALESCE, like so:

   WITH CTE AS
   (
      SELECT 
          CASE WHEN Actual = 0 THEN NULL ELSE Actual END AS Actual,
          CASE WHEN Estimated = 0 THEN NULL ELSE Estimated END AS Estimated,
          CASE WHEN Original = 0 THEN NULL ELSE Original END AS Original
      FROM MyTable
   )
    SELECT DATEPART(YEAR, Date), DATEPART(MONTH, Date), 
           SUM(Coalesce(Actual, Estimated, Original)) 
    FROM CTE
    GROUP BY 
       DATEPART(YEAR, Date), DATEPART(MONTH, Date);

Upvotes: 2

Related Questions