Solved

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

Posted on 2006-11-20
20
14,802 Views
Last Modified: 2012-05-05
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...

Help please...?


0
Comment
Question by:Lee W, MVP
  • 8
  • 7
  • 4
  • +1
20 Comments
 
LVL 35

Accepted Solution

by:
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

by:Raynard7
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

by:Lee W, MVP
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

by:Raynard7
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

by:dduser
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

by:Lee W, MVP
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

by:Raynard7
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

by:Lee W, MVP
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

by:dduser
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

by:Lee W, MVP
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 35

Expert Comment

by:Raynard7
ID: 17978367
If you had

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

by:Raynard7
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

by:Lee W, MVP
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

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

Regards,

dduser
0
 
LVL 95

Author Comment

by:Lee W, MVP
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

by:dduser
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

by:Lee W, MVP
ID: 17978407
I'm beat - I'll revisit this in the morning... thanks for helping guys.

-Lee
0
 
LVL 28

Expert Comment

by:imran_fast
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

by:Raynard7
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

by:Lee W, MVP
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now