Solved

# How to return a zero from a SUM function.

Posted on 2005-04-24
168 Views
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
Question by:JohnHowlett

LVL 34

Expert Comment

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

LVL 34

Expert Comment

correction...

replace

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

with

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

LVL 28

Expert Comment

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

LVL 4

Author Comment

I have tried both, but neither method worked.  I am still getting returned only those months that contain non-zero values.
0

LVL 28

Expert Comment

Can you please post your latest SQL statement.  Make sure you used LEFT OUTER JOIN instead of just INNER JOIN.
0

LVL 4

Author Comment

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

LVL 28

Accepted Solution

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

LVL 68

Expert Comment

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

LVL 68

Expert Comment

CORRECTION (typo):

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

LVL 4

Author Comment

Brilliant!! Thank you, rafrancisco.
0

LVL 4

Author Comment

(....you've also introduced me to user-defined functions...!)
0

## Featured Post

### Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve thâ€¦
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.