Solved

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

Posted on 2006-11-20
20
14,811 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Upgrading my SSIS package in VS 2012 6 59
SQL Activity Monitor detail 2 24
Error when creating a table from a function 6 17
SQLCMD Syntax 2 13
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

839 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