Returning a zero COUNT - 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

General SQL Server Forums

New to SQL Server Programming

Returning a zero COUNT

Author

Topic

dorado77
Starting Member

3 Posts

Posted - 2009-08-10 : 07:34:15

I am making a query on a table
SELECT COUNT(A) AS [COUNT] FROM TABLE

It returns
[Count]
4
for a count of four but returns
[Count]
No rows for a count of 0.

I need it to return
[Count]
0
for a count of 0.
Any Ideas please ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-10 : 07:37:39

Please tell us the whole story

DECLARE	@Sample TABLE
	(
		i INT
	)

SELECT	COUNT(i) AS [COUNT]
FROM	@Sample

N 56°04'39.26"
E 12°55'05.63"

dorado77
Starting Member

3 Posts

Posted - 2009-08-10 : 07:49:44

DECLARE TABLE Records
(
country varchar(50)
)
insert into Records (US)
insert into Records (US)
insert into Records (UK)
insert into Records (UK)
insert into Records (US)

select count(country) AS [Count]
where country = 'US'
returns
[Count]
3
select count(country) AS [Count]
where country = 'UK'
returns
[Count]
2
But
select count(country) AS [Count]
where country = 'China'
returns
[Count]

I need it to return something like
[Count]
0
with the '0' in there.
Cheers !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-10 : 07:55:39

Yes. What is it that doesn't work for you?
Please tell us the full picture!

DECLARE	@Records TABLE
	(
		Country VARCHAR(50)
	)

INSERT	@Records
SELECT	'US' UNION ALL
SELECT	'US' UNION ALL
SELECT	'UK' UNION ALL
SELECT	'UK' UNION ALL
SELECT	'US'

select count(country) AS [Count]
from @records
where country = 'US'

select count(country) AS [Count]
from @records
where country = 'UK'

select count(country) AS [Count]
from @records
where country = 'China'

N 56°04'39.26"
E 12°55'05.63"

dorado77
Starting Member

3 Posts

Posted - 2009-08-10 : 08:13:24

select count(country) AS [Count] from @records
where country = 'China' (example)
returns
[Count]

I need it to return something like
[Count]
0
with the '0' in there.
Cheers !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-10 : 08:29:58

Please. Copy and Paste the suggestion above.
You will see that for "China" the database engine returns a 0 as resultset.
A single column, single row resultset.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources