Solved

COALESCE and ISNULL Functions Not Returning 0 Value

Posted on 2006-06-15
6
1,301 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
  • 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 40 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 460 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Upgrading my SSIS package in VS 2012 6 60
Re-appearing SQL Server Agent jobs 7 30
SQL Backup Question 2 30
RESTORE A BACKUP IN SQL 2012 from SQL 2008 9 65
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

828 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