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?
LVL 4
JohnHowlettAsked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
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
 
Brian CroweDatabase AdministratorCommented:
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 CroweDatabase AdministratorCommented:
correction...

replace

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

with

SELECT     month, SUM(IsNull(dbo.Tbl_Actual.Effort, 0)) AS Total
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.