October 31, 2017 at 4:45 am
Hi Guys.
I have the following as part of my statement and it counts the '1900-01-01 00:00:00.000' fine. However, it does not also count the NULLs. Can you help? The column contains both NULL and '1900-01-01 00:00:00.000'. Its worth noting i only have read only access to the tables and did not design it. I am just querying the database.
Thanks
SUM (CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN 0 WHEN NULL THEN 0 ELSE 1 END) AS CLOSED
October 31, 2017 at 4:54 am
Considering you're doing a COUNT, why not instead do:COUNT(CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN NULL ELSE InspectionClosed END)
NULLs are not counted when using the aggragate, so making those values NULL as well, will exclude them.
If you want to keep the SUM operator to do a COUNT, then you could do:SUM(CASE WHEN InspectionClosed = '1900-01-01 00:00:00.000' OR InspectionClosed IS NULL THEN 0 ELSE 1 END)
I do, however, prefer to use COUNT when doing a count. 😉
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 31, 2017 at 5:02 am
Sounds like an ANSI_NULLS setting thing... Some sample code:
DECLARE @ClosedStuff TABLE (InspectionClosed DATE NULL);
INSERT INTO @ClosedStuff
VALUES (NULL),(NULL),('19000101'),(GETDATE())
SELECT * FROM @ClosedStuff
SET ANSI_NULLS ON
SELECT SUM(CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN 0 WHEN NULL THEN 0 ELSE 1 END) AS CLOSED
FROM @ClosedStuff -- returns 3
SET ANSI_NULLS OFF
SELECT SUM(CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN 0 WHEN NULL THEN 0 ELSE 1 END) AS CLOSED
FROM @ClosedStuff -- returns 1
The first SELECT SUM... returns 3, the second returns 1.
Try using:SELECT SUM(CASE ISNULL(InspectionClosed,'1900-01-01 00:00:00.000') WHEN '1900-01-01 00:00:00.000' THEN 0 ELSE 1 END) AS CLOSED
Thomas Rushton
blog: https://thelonedba.wordpress.com
October 31, 2017 at 5:13 am
Thank you guys both work great
October 31, 2017 at 8:21 am
ThomasRushton - Tuesday, October 31, 2017 5:02 AMSounds like an ANSI_NULLS setting thing... Some sample code:
DECLARE @ClosedStuff TABLE (InspectionClosed DATE NULL);
INSERT INTO @ClosedStuff
VALUES (NULL),(NULL),('19000101'),(GETDATE())SELECT * FROM @ClosedStuff
SET ANSI_NULLS ON
SELECT SUM(CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN 0 WHEN NULL THEN 0 ELSE 1 END) AS CLOSED
FROM @ClosedStuff -- returns 3SET ANSI_NULLS OFF
SELECT SUM(CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN 0 WHEN NULL THEN 0 ELSE 1 END) AS CLOSED
FROM @ClosedStuff -- returns 1The first SELECT SUM... returns 3, the second returns 1.
Try using:
SELECT SUM(CASE ISNULL(InspectionClosed,'1900-01-01 00:00:00.000') WHEN '1900-01-01 00:00:00.000' THEN 0 ELSE 1 END) AS CLOSED
Setting ANS_NULLS has been deprecated. You should not be changing the setting for ANSI_NULLS. SET ANSI_NULLS
I would rewrite this as followsSELECT SUM(CASE WHEN InspectionClosed <> '1900-01-01 00:00:00.000' THEN 1 ELSE 0 END) AS CLOSED
FROM @ClosedStuff -- returns 1
The other option is to use the correct test for NULL values:SELECT SUM(CASE WHEN InspectionClosed = '1900-01-01 00:00:00.000' THEN 0 WHEN InspectionClosed IS NULL THEN 0 ELSE 1 END) AS CLOSED
FROM @ClosedStuff -- returns 1
As you can see, this requires two tests, instead of one for the first approach. NOTE: Both rewrites use the other form of the CASE expression. I find that I rarely use the simple CASE expression, because I'm rarely testing only one field.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 31, 2017 at 8:24 am
Why not use NULLIF with a COUNT?
DECLARE @ClosedStuff TABLE (InspectionClosed DATE NULL);
INSERT INTO @ClosedStuff
VALUES (NULL),(NULL),('19000101'),(GETDATE())
SELECT COUNT(NULLIF( InspectionClosed, '19000101')) FROM @ClosedStuff
October 31, 2017 at 8:32 am
Luis Cazares - Tuesday, October 31, 2017 8:24 AMWhy not use NULLIF with a COUNT?
DECLARE @ClosedStuff TABLE (InspectionClosed DATE NULL);
INSERT INTO @ClosedStuff
VALUES (NULL),(NULL),('19000101'),(GETDATE())SELECT COUNT(NULLIF( InspectionClosed, '19000101')) FROM @ClosedStuff
I always forget about NULLIF... :Whistling:
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 29, 2024 at 7:08 am
This was removed by the editor as SPAM
August 31, 2024 at 7:09 am
This was removed by the editor as SPAM
September 15, 2024 at 7:05 am
This was removed by the editor as SPAM
November 14, 2024 at 3:01 pm
Sounds like an ANSI_NULLS setting thing... Some sample code:
DECLARE @ClosedStuff TABLE (InspectionClosed DATE NULL);INSERT INTO @ClosedStuff
VALUES (NULL),(NULL),('19000101'),(GETDATE())
SELECT * FROM @ClosedStuff
SET ANSI_NULLS ON
SELECT SUM(CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN 0 WHEN NULL THEN 0 ELSE 1 END) AS CLOSED
FROM @ClosedStuff -- returns 3
SET ANSI_NULLS OFF
SELECT SUM(CASE InspectionClosed WHEN '1900-01-01 00:00:00.000' THEN 0 WHEN NULL THEN 0 ELSE 1 END) AS CLOSED
FROM @ClosedStuff -- returns 1
The first SELECT SUM... returns 3, the second returns 1.
Try using:
SELECT SUM(CASE ISNULL(InspectionClosed,'1900-01-01 00:00:00.000') WHEN '1900-01-01 00:00:00.000' THEN 0 ELSE 1 END) AS CLOSED
thanks for share very helpfully post.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply