Solved

# SUM(Field) = NULL - how to get it to = 0

Posted on 2006-11-20
14,802 Views
I've got something that's probably simple, but I'm still not that great at SQL...

Sample query:
SELECT SUM(field1) + SUM(field2) AS 'Total' FROM Table WHERE field3 = 'something'

Problem is, sometimes, field1 or field2 may not have any entries when field3 = 'something'.

For example:

field1   field2   field3
10        20         b
NULL    15         a
5          30        b
NULL     25        a

Now the problem is, using the above query, SELECT SUM(field1) + SUM(field2) AS 'Total' FROM Table WHERE field3 = 'a'  Results in NULL and not what I need, 40.  I was thinking something like CASE WHEN Sum(field1) IS NULL THEN 0 - but I think I have the syntax wrong...

0
Question by:Lee W, MVP
• 8
• 7
• 4
• +1

LVL 35

Accepted Solution

Raynard7 earned 200 total points
ID: 17978281
you could do a few things

Select sum(coalesce(field,0)) + sum(coalesce(field2, 0)) as Total from Table where field3 = 'a'
0

LVL 35

Expert Comment

ID: 17978286
Or you could use IFNULL to perform the same task,

Alternativley the case syntax would be

Select sum(case when field1 is null then 0 else field1 end)) + sum(case when field2 is null then 0 else field2 end) as Total from Table where field3 = 'a'
0

LVL 95

Author Comment

ID: 17978295
Ok, I figured THIS out earlier - THIS is what I want (end results), but I also need to cycle it through for EVERY team (to create a standings type output, ordered by 'Won'

DECLARE @Team TINYINT
DECLARE @WonLeft TINYINT
DECLARE @WonRight TINYINT
DECLARE @LostLeft TINYINT
DECLARE @LostRight TINYINT
SELECT @Team = 1

IF (SELECT SUM(RightPoints) FROM Matches WHERE RightTeam = @Team) IS NULL
SELECT @WonRight = 0
ELSE
SELECT @WonRight = (SELECT SUM(RightPoints) FROM Matches WHERE RightTeam = @Team)

IF (SELECT SUM(LeftPoints) FROM Matches WHERE LeftTeam = @Team) IS NULL
SELECT @WonLeft = 0
ELSE
SELECT @WonLeft = (SELECT SUM(LeftPoints) FROM Matches WHERE LeftTeam = @Team)

IF (SELECT SUM(RightPoints) FROM Matches WHERE LeftTeam = @Team) IS NULL
SELECT @LostLeft = 0
ELSE
SELECT @LostLeft = (SELECT SUM(RightPoints) FROM Matches WHERE LeftTeam = @Team)

IF (SELECT SUM(LeftPoints) FROM Matches WHERE RightTeam = @Team) IS NULL
SELECT @LostRight = 0
ELSE
SELECT @LostRight = (SELECT SUM(LeftPoints) FROM Matches WHERE RightTeam = @Team)

SELECT @Team AS 'Team', @WonLeft + @WonRight AS 'Won', @LostLeft + @LostRight AS 'Lost'

RESULTS:
Team:   Won:   Lost:
1           21       23
0

LVL 35

Expert Comment

ID: 17978296
or you could do

Select sum(coalesce(field,0) + coalesce(field2, 0)) as Total from Table where field3 = 'a'
0

LVL 9

Expert Comment

ID: 17978301
Select Sum(IsNull(field1,0)) + Sum(IsNull(field2,0)) As Total from Table where field3 = 'something'

Regards,

dduser
0

LVL 95

Author Comment

ID: 17978305
Raynard7 - hadn't seen your posts (didn't expect a fast answer this early in the AM - EST).  I'll look over it.
0

LVL 35

Expert Comment

ID: 17978315
I'd suggest that you could do this in one query without having to do the stored procedure, it does not seem too complex.

I'm a little unsure the relationship between rightpoints and wins and leftpoints and wins. But with sub queries this would not be hard to emulate.
0

LVL 95

Author Comment

ID: 17978331
Ok, I don't think the second comment will work... I realized I may have been unintentially misleading... Here's the EXACT data being used.

Match-LeftTeam-RightTeam-LeftPoints-RightPoints
1      7      2      11      0
2      6      8      6      5
3      1      3      4      7
4      1      2      6      5
5      2      4      8      3
6      5      6      5      6
7      7      8      5      6
8      5      7      6      5
9      3      4      8      3
10      1      8      3      8
11      2      6      11      0
12      7      3      3      8
13      5      4      7      4
14      8      4      5      6
15      3      6      11      0
16      1      5      8      3

If you'll note, the right team has NEVER been Team 1, so when I SELECT SUM(RightPoints) FROM Matches WHERE RightTeam = 1 I get nothing - there are no null fields to sum, but I get null because no records are returned.  Follow?
0

LVL 9

Expert Comment

ID: 17978350
Select r.Team,WonRight,WonLeft,WonRight+WonLeft as Won,LostRight,LostLeft,LostRight + LostLeft as Lost from (Select RightTeam as Team,sum(RightPoints) as WonRight,sum(LeftPoints) as LostRight from Matches group by RightTeam) as inner join (Select LeftTeam as Team,sum(LeftPoints) as WonLeft,sum(RightPoints) as LostLeft from Matches group by LeftTeam) as l on r.Team = l.Team where Team = @Team

Regards,

dduser

Check this out...
0

LVL 95

Author Comment

ID: 17978363
Ok, COALESCE is exactly what I was looking for for part 1... now I just need to figure out how to get things to look through the list of teams ordered by Won (as I mentioned in the top of my second comment.
0

LVL 35

Expert Comment

ID: 17978367

Select
q.Team,
(select sum(case when m.leftTeam = q.LeftTeam and m.LeftPoints > m.RightPoints then 1 when m.rightTeam = q.rightTeam and m.LeftPoints < m.RightPoints then 1 else 0 end from Matches as m) as Wins,
sum(case when m.leftTeam = q.LeftTeam and m.LeftPoints < m.RightPoints then 1 when m.rightTeam = q.rightTeam and m.LeftPoints > m.RightPoints then 1 else 0 end from Matches as m) as Losses,
sum(case when m.leftTeam = q.LeftTeam and m.LeftPoints = m.RightPoints then 1 when m.rightTeam = q.rightTeam and m.LeftPoints = m.RightPoints then 1 else 0 end from Matches as m) as Draws
from
(
Select LeftTeam as Team From Matches
union
Select RightTeam as Team From Matches
) as q

this should give you the wins draws and losses for each team competing.
You could of course restrict this to just one team by changing the sub query to just (1 is the team id)
select 1 as Team
0

LVL 35

Expert Comment

ID: 17978371
BTW Coalesce will look through values in a list and return the first (reading left to right) that is not null.
Nullif looks at the first expression if it is null then gives the second.
0

LVL 95

Author Comment

ID: 17978377
Raynard7,

I cut and paste the query and got this:

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'from'.
Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'as'.
0

LVL 9

Expert Comment

ID: 17978384
Raynard's query or mine??? Did you tried mine??

Regards,

dduser
0

LVL 95

Author Comment

ID: 17978397
dduser,

I cut and paste your query, formatting it as follows (just extra Carriage Returns) and I got this:
Server: Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'inner'.
Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'as'.

Select
r.Team,
WonRight,
WonLeft,
WonRight+WonLeft as Won,
LostRight,
LostLeft,
LostRight + LostLeft as Lost
from
(Select RightTeam as Team,sum(RightPoints) as WonRight,sum(LeftPoints) as LostRight from Matches group by RightTeam) as
inner join
(Select LeftTeam as Team,sum(LeftPoints) as WonLeft,sum(RightPoints) as LostLeft from Matches group by LeftTeam) as l on r.Team = l.Team
where
Team = @Team
0

LVL 9

Expert Comment

ID: 17978401
Select
r.Team,
WonRight,
WonLeft,
WonRight+WonLeft as Won,
LostRight,
LostLeft,
LostRight + LostLeft as Lost
from
(Select RightTeam as Team,sum(RightPoints) as WonRight,sum(LeftPoints) as LostRight from Matches group by RightTeam) as r
inner join
(Select LeftTeam as Team,sum(LeftPoints) as WonLeft,sum(RightPoints) as LostLeft from Matches group by LeftTeam) as l on r.Team = l.Team
where
Team = @Team
0

LVL 95

Author Comment

ID: 17978407
I'm beat - I'll revisit this in the morning... thanks for helping guys.

-Lee
0

LVL 28

Expert Comment

ID: 17978723
hi leew,
Try something like this

select
sum(Case when RightTeam= 1 then RightPoints else 0 end ) right_points,
sum(Case when LeftTeam= 1 then LeftPointselse 0 end ) Left_points
from Matches

0

LVL 35

Expert Comment

ID: 17982653
Sorry, some brackets were in the wrong spot

SELECT q.Team,
(SELECT SUM(CASE
WHEN m.leftTeam = q.LeftTeam
AND m.LeftPoints > m.RightPoints THEN 1
WHEN m.rightTeam = q.rightTeam
AND m.LeftPoints < m.RightPoints THEN 1
ELSE 0
END)
FROM   Matches AS m) AS Wins,
(SELECT SUM(CASE
WHEN m.leftTeam = q.LeftTeam
AND m.LeftPoints < m.RightPoints THEN 1
WHEN m.rightTeam = q.rightTeam
AND m.LeftPoints > m.RightPoints THEN 1
ELSE 0
END)
FROM   Matches AS m) AS Losses,
(SELECT SUM(CASE
WHEN m.leftTeam = q.LeftTeam
AND m.LeftPoints = m.RightPoints THEN 1
WHEN m.rightTeam = q.rightTeam
AND m.LeftPoints = m.RightPoints THEN 1
ELSE 0
END)
FROM   Matches AS m) AS Draws
FROM   (SELECT LeftTeam AS Team
FROM   Matches
UNION
SELECT RightTeam AS Team
FROM   Matches) AS q
0

LVL 95

Author Comment

ID: 18023635
Hey folks, the original question has been answered... so I'm closing this one.  I've opened a new question here related to this one:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_22073517.html

Would appreciate any thoughts...
0

## Featured Post

### Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.