COALESCE and ISNULL Functions Not Returning 0 Value

Hi,

I'm using SQL Server 2000.  I would like my RecordCount column to return a 0 value.  The RecordCount is a numeric value.  I've tried the COALESCE and IsNull functions with no success as follows:


SELECT     COUNT(COALESCE (ItemCount, 0)) AS RecordCount
FROM         dbo.CAR_SummaryTable
GROUP BY ItemCategory
HAVING      (ItemCategory = 'Unincorp Central Region')


SELECT     COUNT(ISNULL(ItemCount, 0)) AS RecordCount
FROM         dbo.CAR_SummaryTable
GROUP BY ItemCategory
HAVING      (ItemCategory = 'Unincorp Central Region')

Any Suggestions?
Thanks,
Denise
DeniseGoodheartAsked:
Who is Participating?
 
ExpertAdminConnect With a Mentor Commented:
I don't think you need a COALESCE or ISNULL at all if you are using a COUNT. Count will ALWAYS return a number, no matter what the value of the column is in the database. Therefore, this should work:

SELECT     COUNT(*) AS RecordCount
FROM         dbo.CAR_SummaryTable
WHERE    (ItemCategory = 'Unincorp Central Region'

Or am I missing the point?

M@
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

SELECT     ISNULL(COUNT(ItemCount), 0) AS RecordCount
FROM         dbo.CAR_SummaryTable
GROUP BY ItemCategory
HAVING      (ItemCategory = 'Unincorp Central Region')
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
note that, when grouping by values, it will not produce rows with 0 at all if there is no row for the grouped by fields...
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DeniseGoodheartAuthor Commented:
Hi ExpertAdmin,

No, I was missing the point.  I'm working on a project where the project due date is based on the budget due date, and not the actual time required for the project.  In any event, sometimes I lose perspective in what I'm doing because I'm trying to meet the aggressive project due date!  I am so thankful for Experts Exchange!

Many Thanks,
Denise
0
 
DeniseGoodheartAuthor Commented:
Hi angelIII:

Many thanks for your useful information!

Denise
0
 
ExpertAdminCommented:
Anytime. Glad I could help. I have been on a few of those projects myself.

M@
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.