Clean way to get 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)

Clean way to get zero

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-19 : 20:44:30

DECLARE @Total INT
SELECT @Total = COUNT(*) FROM MyTable WHERE some condition is not met
SET @Total = IsNull(@Total, 0)

Is there a way to do this in two statements and not get a NULL for nothing found?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-19 : 20:53:40

?

Why are you getting a NULL ? @Total should be zero if the condition isn't met.

Damian
Ita erat quando hic adveni.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-19 : 20:58:26

[crack-smoking yak]

.....Nope, we don't have that one.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-05-19 : 21:14:36

DECLARE @Total INT
SELECT @Total = ISNULL(COUNT(*),0) FROM MyTable WHERE some condition is not met

Is that what your after?? Or have i totally missed the point as usual!!

Andy

Edit: Bollocks i did, if the condition isnt met it would return 0 anyway

Beauty is in the eyes of the beerholder

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-19 : 22:01:56

I guess the devil is in the details as usual

declare @test int

select	@test = count(*) from branches group by branchname having count(*) > 100

Print IsNull(@test, -5)

Will print -5

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-19 : 22:13:46

[crack-smoking yak]

.....Nope, we don't have that one.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-19 : 22:15:13

That query is trying to return a set, not a scalar value.
If you had multiple Branchnames that met that condition your query won't look right anyway.

Damian
Ita erat quando hic adveni.

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-19 : 22:35:48

quote:
Originally posted by Merkin

That query is trying to return a set, not a scalar value.
If you had multiple Branchnames that met that condition your query won't look right anyway.

The query returns the correct count when it's non-zero.

Lemme rethink this and post back later.

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-19 : 22:39:49

[code]declare @test int

SELECT @test = COUNT(*)
FROM (
SELECT Branchname FROM Branches GROUP BY Branchname HAVING COUNT(*) > 100
) X

Print IsNull(@test, -5)
[/code]
OK. This query prints zero.

The previous query

[code]select @test = count(*) from branches group by branchname having count(*) > 100[/code]
Read clearly to me at first, but on closer inspection, there's a difference in the two COUNT(*). The first should be the count of Branchnames having duplicates, the second is the COUNT of each individual Branchname.

Hmmm...

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-19 : 22:49:33

[code]select @test = count(*) from branches group by branchname having count(*) > 100[/code]
So, the way this query reads, if no records are returned in the recordset formed by the GROUP BY, then the @test = COUNT(*) isn't executed at all.

Put differently

SELECT COUNT(*) FROM MyTable WHERE 1 = 0 -- Returns a recordset of 1 row with zero as the column value

SELECT COUNT(*) FROM MyTable GROUP BY MyColumn HAVING COUNT(*) < 0 -- Returns a recordset of zero rows.

SELECT COUNT(*) FROM MyTable GROUP BY MyColumn HAVING COUNT(*) >= 0 -- ~~Returns a recordset of 1 row with the total (COUNT(*)) of DISTINCT MyColumn values.~~ Wrong !

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-19 : 23:25:09

It makes sense when you see what those queries return WITH data.

Assuming :

create table branches (
branchid int,
branchname varchar(100)
)

INSERT branches VALUES (1, 'a')
INSERT branches VALUES (2, 'a')
INSERT branches VALUES (3, 'a')
INSERT branches VALUES (4, 'b')
INSERT branches VALUES (5, 'b')
INSERT branches VALUES (6, 'c')

If I run :
select count(*) from branches
I get 6, which is a count of all the rows.

If I run :

select count(*) from branches WHERE 1=0
I get 0, which is correct, there are zero rows that meet this criteria, so the count is zero.

If I run this :

select count(*) from branches group by branchname

I get :
3
2
1

Which is counts of branch rows, grouped by branch name.

If I only want to return rows where I have more than one of the same branchname

select count(*) from branches group by branchname having count(*) > 1

I get :

3
2

This is because the having clause is an extra level of filtering AFTER everything else has been evaluated.

SO

select count(*) from branches group by branchname having count(*) > 100

First gets a set (the result of select count(*) from branches group by branchname )
then filters off the rows that don't meet the count condition, removing them from your result. You will not get a single count, because you asked for multiple counts, but they were filtered out of your result set.

I said earlier your query was flawed because if you have multiple branchnames with more than 100 instances your number will be wrong. Try this :

Declare @Count int
select @Count = count(*) from branches group by branchname having count(*) > 1
Select @Count

Your result will be a 2, which is the count of branchname = 'b'
This is because @Count got set to 3 with the first row returned, then set to 2 with the second row.

Damian
Ita erat quando hic adveni.

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-19 : 23:33:55

Thanks Damian,

I see it now, it helped me to write

SELECT Branchname, count(*) from branches group by branchname having count(*) > 1

Which should return a row for every branchname, along with a count for each...

I got it.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-05-20 : 02:59:56

I understood that COUNT(1) was more efficient that COUNT(*) - any of the more enlightened yaks have any comments?

A sarcasm detector, what a great idea.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-05-20 : 04:46:59

quote:
Originally posted by elwoos

I understood that COUNT(1) was more efficient that COUNT(*) - any of the more enlightened yaks have any comments?

A sarcasm detector, what a great idea.

I think that is an old myth from the days when computers were slow,
and programmers thought that count(*) meant that You had to count all
the columns as well, to make sure they all had data.

If a table only had data in half of it's columns
count(*) would return 1/2 !,
those were crazy days.

(it may or may not have been true for other versions of DBMS though)

rockmoose

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-20 : 07:00:05

there are other databases that optimise count(1) better than count(*) (but I believe some have been corrected now) but sql server gets it right.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-20 : 07:33:58

quote:
Originally posted by SamC

I guess the devil is in the details as usual

declare @test int

select @test = count(*) from branches group by branchname having count(*) > 100

Print IsNull(@test, -5)

Will print -5

Sam, is this you wanted?

DECLARE @test varchar(10)
select @test = case when count(*) =0 then null else count(*) end from branches group by branchname having count(*) > 100
print IsNull(@test, -5)

Madhivanan

Failing to plan is Planning to fail

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-20 : 08:05:39

Hehe

Test that code Madhivanan, it doesn't work right.
Also, we solved this prob a few posts up

Damian
Ita erat quando hic adveni.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-05-20 : 09:03:48

quote:
Originally posted by Merkin

Hehe

Test that code Madhivanan, it doesn't work right.
Also, we solved this prob a few posts up

Damian
Ita erat quando hic adveni.

Merkin, I think it will work

Try this

If exists(select * from sysobjects where name='branches')
drop table branches
Go
create table branches (
branchid int,
branchname varchar(100)
)
Go
Set nocount on
INSERT branches VALUES (1, 'a')
INSERT branches VALUES (2, 'a')
INSERT branches VALUES (3, 'a')
INSERT branches VALUES (4, 'b')
INSERT branches VALUES (5, 'b')
INSERT branches VALUES (6, 'c')
Set nocount off
Go
DECLARE @test varchar(10)
SELECT @test = case when count(*)=0 then null else count(*) end from branches group by branchname having count(*) > 100
print IsNull(@test, -5)

Madhivanan

Failing to plan is Planning to fail

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-20 : 09:14:20

You get -5. The whole point of this thread was that sam wanted it to be 0. Which we figured out was a flawed requirement anyway.

Damian
Ita erat quando hic adveni.

SamC
White Water Yakist

3467 Posts

Posted - 2005-05-20 : 11:23:07

quote:
Originally posted by Merkin

You get -5. The whole point of this thread was that sam wanted it to be 0. Which we figured out was a flawed requirement anyway.

I have a theory that Damian isn't human. No one has ever known him to sleep.

Not to pick, but this wasn't a flawed requirement as much as my misunderstanding. The requirement was to find a count of all names that had duplicates. I was mistaking

DECLARE @Total INT
SELECT @Total = COUNT(*) FROM Branches GROUP BY Branchname HAVING COUNT(*) > 1

@Total is not the number of names with duplicates, but is the number of duplicates of the LAST duplicate name in the recordset. Instead...

declare @test int

SELECT	@test = COUNT(*)
	FROM	(
		SELECT Branchname FROM Branches GROUP BY Branchname HAVING COUNT(*) > 1
	) X

Correctly returns the number of names in table Branches which have duplicates. (Is there a shorter way to write this query?)

In my case, the mistake was an annoyance since either query will correctly return non-zero value if duplicates exist and NULL or zero if no duplicates exist.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources