Count(*)=0

AlHal2
AlHal2 used Ask the Experts™
on
Using the AdventureWorks database this query gives 3 rows.

select count(addressline1) from person.address
where addressline1='1970 Napa Ct.'
group by addressline1

Open in new window


Suppose I change addressline1 from '1970 Napa Ct.' to 'xxx'.  It will return nothing.
How can I amend the query to return a number zero if the address does not exist?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Remove GROUP BY from your query and it will return correct results.
You would need GROUP BY only when the column is listed in the SELECT list, e.g.:
select addressline1, count(*) from person.address
where addressline1 LIKE '1970%'
group by addressline1

Open in new window

Senior Developer
Commented:
As a query can only return (existing) data, you need a second query:

SELECT  ISNULL(
    (
        SELECT  COUNT(*)
        FROM    person.address
        WHERE   addressline1 = '1970 Napa Ct.'
    ), 0) AS Cnt;

Open in new window

@ste5an Could you please tell when the COUNT(*) returns NULL?

Author

Commented:
Thanks.
ste5anSenior Developer

Commented:
oops..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial