• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 177
  • Last Modified:

How to return a zero from a SUM function.

I have got an SQL statement as follows

SELECT     TheMonth, SUM(Effort) AS Total
FROM         dbo.Tbl_Actual
WHERE     (SectionID = 16)
GROUP BY TheMonth, SectionID


The result of theis query is:
TheMonth                    Total
1                                12
6                                12
11                              72
12                              98

I would like the output to be:
TheMonth                     Total
1                                  12
2                                   0
3                                   0
4                                   0
5                                   0
6                                   12
etc.
I.e. I would like to get 0 as the result of the SUM function. I have a workaround, but surely there is an SQL solution....
Any suggestions?
0
JohnHowlett
Asked:
JohnHowlett
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Brian CroweCommented:
DECLARE @month TABLE (
     month int
)

INSERT INTO @month (month) VALUES (1)
INSERT INTO @month (month) VALUES (2)
INSERT INTO @month (month) VALUES (3)
INSERT INTO @month (month) VALUES (4)
INSERT INTO @month (month) VALUES (5)
INSERT INTO @month (month) VALUES (6)
INSERT INTO @month (month) VALUES (7)
INSERT INTO @month (month) VALUES (8)
INSERT INTO @month (month) VALUES (9)
INSERT INTO @month (month) VALUES (10)
INSERT INTO @month (month) VALUES (11)
INSERT INTO @month (month) VALUES (12)

SELECT     month, SUM(dbo.Tbl_Actual.Effort) AS Total
FROM @month AS M
LEFT OUTER JOIN dbo.Tbl_Actual
     ON M.month = dbo.Tbl_Actual.TheMonth
WHERE dbo.Tbl_Actual.SectionID = 16
GROUP BY M.month, dbo.Tbl_Actual.SectionID

0
 
Brian CroweCommented:
correction...

replace

SELECT     month, SUM(dbo.Tbl_Actual.Effort) AS Total

with

SELECT     month, SUM(IsNull(dbo.Tbl_Actual.Effort, 0)) AS Total
0
 
rafranciscoCommented:
If there is no data from you table for a particular month, it will never be included in the output.  For you to have all the months in the output, you can create a user-defined function that returns a table for all the months, like this:

CREATE FUNCTION dbo.GetMonths ()
RETURNS @vMonths TABLE ( TheMonth INT )
AS
BEGIN

DECLARE @vTheMonth INT

SET @vTheMonth = 1
WHILE @vTheMonth <= 12
BEGIN
    INSERT INTO @vMonths (TheMonth) VALUES (@vTheMonth)
    SET @vTheMonth = @vTheMonth + 1
END

RETURN
END
GO

Then you can join this with your table to get your desired output:

SELECT     A.TheMonth, SUM(ISNULL(B.Effort, 0)) AS Total
FROM       dbo.GetMonths() A LEFT OUTER JOIN dbo.Tbl_Actual B
    ON A.TheMonth = B.TheMonth
WHERE     (B.SectionID = 16)
GROUP BY A.TheMonth

By the way, I removed SectionID in your GROUP BY because it is not included in your SELECT.

Hope this helps.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
JohnHowlettAuthor Commented:
I have tried both, but neither method worked.  I am still getting returned only those months that contain non-zero values.
0
 
rafranciscoCommented:
Can you please post your latest SQL statement.  Make sure you used LEFT OUTER JOIN instead of just INNER JOIN.
0
 
JohnHowlettAuthor Commented:

SELECT     A.TheMonth, SUM(ISNULL(B.Effort, 0)) AS Total
FROM       dbo.GetMonths() A LEFT OUTER JOIN dbo.Tbl_Actual B
    ON A.TheMonth = B.TheMonth
WHERE     (B.SectionID = 1)
GROUP BY A.TheMonth
0
 
rafranciscoCommented:
This should now work.

SELECT     A.TheMonth, SUM(ISNULL(B.Effort, 0)) AS Total
FROM       dbo.GetMonths() A LEFT OUTER JOIN dbo.Tbl_Actual B
    ON A.TheMonth = B.TheMonth AND B.SectionID = 1
GROUP BY A.TheMonth
0
 
Scott PletcherSenior DBACommented:
DECLARE @months TABLE (
     TheMonth INT
)
INSERT INTO @months VALUES (1)
INSERT INTO @months VALUES (2)
INSERT INTO @months VALUES (3)
INSERT INTO @months VALUES (4)
INSERT INTO @months VALUES (5)
INSERT INTO @months VALUES (6)
INSERT INTO @months VALUES (7)
INSERT INTO @months VALUES (8)
INSERT INTO @months VALUES (9)
INSERT INTO @months VALUES (10)
INSERT INTO @months VALUES (11)
INSERT INTO @months VALUES (12)


SELECT Mths.TheMonth, SUM(ISNULL(act.Effort, 0)) AS [Total]
FROM @month AS Mths
LEFT OUTER JOIN dbo.Tbl_Actual act
    ON Mths.TheMonth = act.TheMonth
WHERE act.SectionID IS NULL OR act.SectionID = 16
GROUP BY Mths.TheMonth
0
 
Scott PletcherSenior DBACommented:
CORRECTION (typo):  
FROM @months  --[instead of @month]


Comments:
Since the Tbl_Actual row may not be found, you have to check for a SectionID of NULL (not found) as well as 16.
0
 
JohnHowlettAuthor Commented:
Brilliant!! Thank you, rafrancisco.
0
 
JohnHowlettAuthor Commented:
(....you've also introduced me to user-defined functions...!)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now