September 13, 2006 at 8:48 pm
SELECT
P.[ProgramName],
CASE
WHEN SUM([CasesReceived]) IS NULL THEN 0
ELSE SUM([CasesReceived])
END AS TotalCasesReceived,
CASE
WHEN SUM([CasesActive]) IS NULL THEN 0
ELSE SUM([CasesActive])
END AS TotalCasesActive
FROM Programs AS P
LEFT OUTER JOIN SummaryTotals AS ST
ON P.[ProgramID] = ST.[ProgramID]
GROUP BY P.[ProgramName]
ORDER BY P.[ProgramName]
Hello, is there an efficient way to write this instead of repeating the same SUM statement for both WHEN and ELSE?
Thank you
September 13, 2006 at 9:42 pm
SELECT
P.[ProgramName],
, SUM(isnull([CasesReceived],0)) AS TotalCasesReceived,
, SUM(isnull([CasesActive],0)) AS TotalCasesActive
FROM
Programs AS P
LEFT
OUTER JOIN SummaryTotals AS ST
ON P.[ProgramID] = ST.[ProgramID]
GROUP
BY P.[ProgramName]
ORDER
BY P.[ProgramName]
September 14, 2006 at 1:51 am
Sum ignores nulls. If you sum a column that contains nulls, you'll get the total of the non-null values and a warning saying null values were eliminated due to aggregation.
The only way sum can return null is if all values in the column are null.
So, for most efficiency...
SELECT P.[ProgramName],
ISNULL(SUM([CasesReceived]),0) AS TotalCasesReceived,
ISNULL(SUM([CasesActive]),0) AS TotalCasesActive
FROM ....
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 14, 2006 at 3:30 pm
you have to think again... where putting isnull before or after the sum function.
September 14, 2006 at 11:50 pm
Why? Sum ignores nulls. It will add up non-null values. The isnull around the sum is just incase the entire column is null or there are no records. That's the only time that sum can return null.
Try it out
CREATE
TABLE TestSum (
Value1 int,
value2 int,
value3 int
)
GO
INSERT
INTO TestSum (Value1, Value2) VALUES (1,1)
INSERT INTO TestSum (Value1, Value2) VALUES (2,2)
INSERT INTO TestSum (Value1, Value2) VALUES (3,null)
INSERT INTO TestSum (Value1, Value2) VALUES (4,null)
INSERT INTO TestSum (Value1, Value2) VALUES (5,5)
INSERT INTO TestSum (Value1, Value2) VALUES (6,null)
INSERT INTO TestSum (Value1, Value2) VALUES (7,7)
GO
SELECT
sum(value1) AS Sum1, sum(value2) AS Sum2, sum(value3) AS Sum2 FROM TestSum
Returns: Sum1 = 28, Sum2 = 15, Sum3 = NULL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 17, 2006 at 3:32 pm
GilaMonst,
I'm not saying that you're wrong. What you got there is correct.
It depends on how you want to treat the null value.
ie. if it is null do you want to gnore it or give it a value (maybe not 0).
September 17, 2006 at 6:33 pm
also if you do :
select sum(value1+value2) -- you get unexpected result.
September 17, 2006 at 7:42 pm
If you do something without knowing what are you doing you'll get unexpected results.
If you do sum(value1)+sum(value2) you'll get what is expected.
But if you do sum(value1+value2) you actually do not aggregate value1 and value2, you aggregare value1+value2, which is quite different.
Do SELECT value1+value2 first, and then you'll realise that result of sum(value1+value2) is quite expected.
_____________
Code for TallyGenerator
September 17, 2006 at 9:57 pm
have you tried this sum(value1) + sum(value3) ?
your query has no control over how null value should be defined.
September 17, 2006 at 11:05 pm
Original sugession you were not agree with was:
SELECT P.[ProgramName],
ISNULL(SUM([CasesReceived]),0) AS TotalCasesReceived,
ISNULL(SUM([CasesActive]),0) AS TotalCasesActive
FROM ....
If we follow this suggession we get:
ISNULL(sum(value1), 0) + ISNULL(sum(value3), 0)
Something wrong with it?
Does the result look like unexpected?
Why anybody has to think again?
_____________
Code for TallyGenerator
September 17, 2006 at 11:57 pm
Ah, sorry. I misunderstood you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply