Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

COALESCE and ISNULL Functions Not Returning 0 Value

Posted on 2006-06-15
6
Medium Priority
?
1,310 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:DeniseGoodheart
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16915623

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

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 160 total points
ID: 16915628
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
 
LVL 7

Accepted Solution

by:
ExpertAdmin earned 1840 total points
ID: 16915760
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DeniseGoodheart
ID: 16915993
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
 

Author Comment

by:DeniseGoodheart
ID: 16916002
Hi angelIII:

Many thanks for your useful information!

Denise
0
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16916039
Anytime. Glad I could help. I have been on a few of those projects myself.

M@
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question