Suppress zero - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Suppress zero

Author

Topic

TimSinnott
Starting Member

48 Posts

Posted - 2002-10-01 : 20:45:17

Hi all,

There are a few zeroes mixed in the result set from this query.
I'm trying to replace any zeros with NULL.

(Using pubs)
....
SELECT
type,
SUM(case when price > 4 then 1 else 0 end) as Expensive,
SUM(case when price < 3 then 1 else 0 end) as Cheapo
FROM titles
GROUP BY type
....

Any ideas? Thanks in advance.

Tim

SamC
White Water Yakist

3467 Posts

Posted - 2002-10-01 : 20:48:06

SELECT
type,
SUM(case when price > 4 then 1 else NULL end) as Expensive,
SUM(case when price < 3 then 1 else NULL end) as Cheapo
FROM titles
GROUP BY type

--------------

Is this it??

Sam

TimSinnott
Starting Member

48 Posts

Posted - 2002-10-01 : 20:56:40

Sam,
By golly, that worked! :)
Somehow, it doesn't seem to me like it should, but it surely does.

Thanks. :D

Tim

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-02 : 03:18:27

suspect that would get errors if ansii warnings on.
If so

select Expensive = case when Expensive = 0 then null else Expensive end ,
Cheapo = case when Cheapo = 0 then null else Cheapo end ,
from
(
SELECT
type,
SUM(case when price > 4 then 1 else 0 end) as Expensive,
SUM(case when price < 3 then 1 else 0 end) as Cheapo
FROM titles
GROUP BY type
) as a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2002-10-02 : 05:21:09

While the rest of the world is trying to avoid NULL's, you're doing the opposite thing!?

I know that there are situations, where some of the suggestions made in SQL books, fail. But stil, why are you prefering NULL's over zeroes?

TimSinnott
Starting Member

48 Posts

Posted - 2002-10-02 : 12:10:26

Thanks, nr. Yes, that looks stronger. I'll try that.

rihardh, the reason I want to use nulls instead of zeros is because I'm using the result set as the basis of a report. The report is more readable when a bunch of zeros don't clutter it up.

Tim

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-02 : 17:35:32

NULLIF(SUM(...),0) is shorter than those CASEs, and removes the need for the derived table.

TimSinnott
Starting Member

48 Posts

Posted - 2002-10-03 : 13:23:11

Thanks, Arnold. How do you mean? I couldn't get that to work.

Tim

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-03 : 14:18:56


SELECT 
type,
nullif(SUM(case when price > 4 then 1 else 0 end),0) as Expensive, 
nullif(SUM(case when price < 3 then 1 else 0 end),0) as Cheapo 
FROM titles 
GROUP BY type

Jay White
{0}

Edited by - Page47 on 10/03/2002 14:19:46

TimSinnott
Starting Member

48 Posts

Posted - 2002-10-03 : 14:41:43

Now I see. Yep, that works great. :D

That NULLIF is pretty handy.

Thanks Arnold, and thanks Jay.

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources