Get the Count function to return zero instead of nothing at all?

11 Jun 2009 11:58 AM

gocat2005

If no rows match the criteria, I want the Count function to return a zero
instead of nothing at all. How do I do that? Eg: this query returns nothing
at all because nobody has a lastname of 'xxx'. I want it to return a count of
zero.

SELECT     LastName, COUNT(LastName) AS CountNames
FROM        dbo.People
GROUP BY LastName
HAVING     (LastName = N'xxx')

11 Jun 2009 12:25 PM

Uri Dimant

It does returns zero
CREATE TABLE #tmp(c int)

SELECT COUNT(c) FROM #tmp

Show quoteHide quote

"gocat2005" <gocat2***@discussions.microsoft.com> wrote in message
news:E79B8223-5830-423A-A41D-9CFD59ACD086@microsoft.com...
> If no rows match the criteria, I want the Count function to return a zero
> instead of nothing at all. How do I do that? Eg: this query returns
> nothing
> at all because nobody has a lastname of 'xxx'. I want it to return a count
> of
> zero.
>
> SELECT     LastName, COUNT(LastName) AS CountNames
> FROM        dbo.People
> GROUP BY LastName
> HAVING     (LastName = N'xxx')

11 Jun 2009 12:44 PM

gocat2005

Not if you add a filter
HAVING (c = 2)

Show quoteHide quote

"Uri Dimant" wrote:

> It does returns zero
> CREATE TABLE #tmp(c int)
>
> SELECT COUNT(c) FROM #tmp
>
>
>
>
>
> "gocat2005" <gocat2***@discussions.microsoft.com> wrote in message
> news:E79B8223-5830-423A-A41D-9CFD59ACD086@microsoft.com...
> > If no rows match the criteria, I want the Count function to return a zero
> > instead of nothing at all. How do I do that? Eg: this query returns
> > nothing
> > at all because nobody has a lastname of 'xxx'. I want it to return a count
> > of
> > zero.
> >
> > SELECT     LastName, COUNT(LastName) AS CountNames
> > FROM        dbo.People
> > GROUP BY LastName
> > HAVING     (LastName = N'xxx')
>
>
>

11 Jun 2009 12:41 PM

Plamen Ratchev

You cannot do that the way you have the query structured, because no
rows exist for that name (and a group for the name value cannot be
created). But you can do something like this:

SELECT COALESCE((
SELECT COUNT(LastName) AS CountNames
FROM dbo.People
GROUP BY LastName
HAVING LastName = N'xxx'), 0) AS cnt;

--
Plamen Ratchev
http://www.SQLStudio.com

11 Jun 2009 1:08 PM

gocat2005

Hi Plamen
That looks promising...I just need to work out how to pass the filter
criteria to the query from a webpage. This may be difficult as the Lastname
column is not available when I use coalesc. Is there a way of including
several columns?

Show quoteHide quote

"Plamen Ratchev" wrote:

> You cannot do that the way you have the query structured, because no
> rows exist for that name (and a group for the name value cannot be
> created). But you can do something like this:
>
> SELECT COALESCE((
> SELECT COUNT(LastName) AS CountNames
> FROM dbo.People
> GROUP BY LastName
> HAVING LastName = N'xxx'), 0) AS cnt;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
>

11 Jun 2009 2:16 PM

Plamen Ratchev

You can create a stored procedure with parameter "@last_name
NVARCHAR(30)" (change to correct length), call the stored procedure from
your web page passing the parameter. Then the query can be like this
(note you can add more columns to the second subquery and then use them
in the final SELECT):

SELECT T.LastName, COALESCE(P.CountNames, 0) AS cnt
FROM (SELECT @last_name) AS T(LastName)
LEFT JOIN (
SELECT LastName, COUNT(*) AS CountNames
FROM dbo.People
GROUP BY LastName) AS P
ON T.LastName = P.LastName;

--
Plamen Ratchev
http://www.SQLStudio.com

11 Jun 2009 2:12 PM

gocat2005

The query result needs to show lastname and count of lastname eg:
Smith 0

Show quoteHide quote

"gocat2005" wrote:

> If no rows match the criteria, I want the Count function to return a zero
> instead of nothing at all. How do I do that? Eg: this query returns nothing
> at all because nobody has a lastname of 'xxx'. I want it to return a count of
> zero.
>
> SELECT     LastName, COUNT(LastName) AS CountNames
> FROM        dbo.People
> GROUP BY LastName
> HAVING     (LastName = N'xxx')