SQL Converting nulls to zeros

Hi experts!

I've thrown together the following view to take a grouped count of individual sales issues and show the results in multiple columns-- a different column for each type of sales issue, grouped by job:

SELECT     dbo.AdamView.JobID, D.ContractPricingIssues, C.ApplianceIssues, A.GreenFolderIssues
FROM         dbo.AdamView LEFT OUTER JOIN
                          (SELECT     COUNT(IssueCategory) AS ApplianceIssues, JobID
                            FROM          dbo.Sales_Issues AS Sales_Issues_2
                            WHERE      (IssueCategory LIKE '04 - Appliance Issues')
                            GROUP BY JobID) AS C ON dbo.AdamView.ID = C.JobID LEFT OUTER JOIN
                          (SELECT     COUNT(IssueCategory) AS ContractPricingIssues, JobID
                            FROM          dbo.Sales_Issues
                            WHERE      (IssueCategory LIKE '02 - Contract Pricing Issues')
                            GROUP BY JobID) AS D ON dbo.AdamView.ID = D.JobID LEFT OUTER JOIN
                          (SELECT     COUNT(IssueCategory) AS GreenFolderIssues, JobID
                            FROM          dbo.Sales_Issues AS Sales_Issues_2
                            WHERE      (IssueCategory LIKE '01 - Green Folder Issues')
                            GROUP BY JobID) AS A ON dbo.AdamView.ID = A.JobID

Open in new window



The problem I'm having is I can't seem to get the 3 computed columns to show zeros instead of nulls in the results. I've tried COALESE and ISNULL, but my results are still coming back with nulls, so I'm wondering if there is something specific that needs to be done when trying to convert the result of a count...

(Using SQL 2005)
Norm-alNetwork EngineerAsked:
Who is Participating?
 
Norm-alConnect With a Mentor Network EngineerAuthor Commented:
Oh geeze, I'm an idiot. Tried moving the ISNULL outside of the SELECT to no avail, and then thought to apply it to the main SELECT statement like so:

SELECT     dbo.View.JobID, ISNULL(D.ContractPricingIssues,0), ISNULL(C.ApplianceIssues,0), ISNULL(A.GreenFolderIssues,0)
FROM         dbo.View LEFT OUTER JOIN
                          (SELECT     COUNT(IssueCategory) AS ApplianceIssues, JobID
                            FROM          dbo.Sales_Issues AS Sales_Issues_2
                            WHERE      (IssueCategory LIKE '04 - Appliance Issues')
                            GROUP BY JobID) AS C ON dbo.AdamView.ID = C.JobID LEFT OUTER JOIN
                          (SELECT     (COUNT(IssueCategory) AS ContractPricingIssues, JobID
                            FROM          dbo.Sales_Issues
                            WHERE      (IssueCategory LIKE '02 - Contract Pricing Issues')
                            GROUP BY JobID) AS D ON dbo.AdamView.ID = D.JobID LEFT OUTER JOIN
                          (SELECT     (COUNT(IssueCategory) AS GreenFolderIssues, JobID
                            FROM          dbo.Sales_Issues AS Sales_Issues_2
                            WHERE      (IssueCategory LIKE '01 - Green Folder Issues')
                            GROUP BY JobID) AS A ON dbo.AdamView.ID = A.JobID

Open in new window


and it works perfectly. Thank you so much for the help/inspiration. :)
0
 
JacobfwCommented:
did you put the ISNULL inside or outside the COUNT?
0
 
Norm-alNetwork EngineerAuthor Commented:
Outside, since the field is an nvarchar and I want to convert the COUNT total to zero and not the value of the field itself.

Here's what I've been trying:

SELECT     dbo.AdamView.JobNum, D.ContractPricingIssues, C.ApplianceIssues, A.GreenFolderIssues
FROM         dbo.AdamView LEFT OUTER JOIN
                          (SELECT     ISNULL(COUNT(IssueCategory), 0) AS ApplianceIssues, JobID
                            FROM          dbo.Sales_Issues AS Sales_Issues_2
                            WHERE      (IssueCategory LIKE '04 - Appliance Issues')
                            GROUP BY JobID) AS C ON dbo.AdamView.ID = C.JobID LEFT OUTER JOIN
                          (SELECT     ISNULL(COUNT(IssueCategory), 0) AS ContractPricingIssues, JobID
                            FROM          dbo.Sales_Issues
                            WHERE      (IssueCategory LIKE '02 - Contract Pricing Issues')
                            GROUP BY JobID) AS D ON dbo.AdamView.ID = D.JobID LEFT OUTER JOIN
                          (SELECT     ISNULL(COUNT(IssueCategory), 0) AS GreenFolderIssues, JobID
                            FROM          dbo.Sales_Issues AS Sales_Issues_2
                            WHERE      (IssueCategory LIKE '01 - Green Folder Issues')
                            GROUP BY JobID) AS A ON dbo.AdamView.ID = A.JobID

Open in new window


It runs fine but the results are all still NULL. This is my first time using ISNULL, so it really wouldn't surprise me if it's just a silly syntax mix up...
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
JacobfwConnect With a Mentor Commented:
try putting the ISNULL around each SELECT

dbo.AdamView LEFT OUTER JOIN
                          ISNULL((SELECT  COUNT
0
 
Norm-alNetwork EngineerAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for Jacobfw's comment http:/Q_27324618.html#36589209
Assisted answer: 0 points for Prec94513's comment http:/Q_27324618.html#36589865

for the following reason:

Not quite the solution, but led me down the path to the correct one.
0
 
Geert GOracle dbaCommented:
doesn't below query work faster ?

and i believe you have a bug in your sql ...
you are using AdamView for a join but not selecting anything from it
SELECT V.JobID, 
    ISNULL(COUNT(CASE WHEN SI.IssueCategory='02 - Contract Pricing Issues' THEN SI.IssueCategory END), 0) ContractPricingIssues,
  ISNULL(COUNT(CASE WHEN SI.IssueCategory='04 - Appliance Issues' THEN SI.IssueCategory END), 0) ApplianceIssues,
  ISNULL(COUNT(CASE WHEN SI.IssueCategory='01 - Green Folder Issues' THEN SI.IssueCategory END), 0) GreenFolderIssues
FROM  dbo.View as V LEFT OUTER JOIN
  dbo.Sales_Issues as SI ON SI.JOBID = V.JOBID
GROUP BY V.JOBID

Open in new window


with your adamview selection, it would look like this ... but very odd
SELECT V.JobID, 
    ISNULL(COUNT(CASE WHEN SI.IssueCategory='02 - Contract Pricing Issues' THEN SI.IssueCategory END), 0) ContractPricingIssues,
  ISNULL(COUNT(CASE WHEN SI.IssueCategory='04 - Appliance Issues' THEN SI.IssueCategory END), 0) ApplianceIssues,
  ISNULL(COUNT(CASE WHEN SI.IssueCategory='01 - Green Folder Issues' THEN SI.IssueCategory END), 0) GreenFolderIssues
FROM  dbo.View as V 
  join dbo.AdamView as av on V.JOBID = av.ID
  LEFT OUTER JOIN dbo.Sales_Issues as SI ON SI.JOBID = V.JOBID
GROUP BY V.JOBID

Open in new window

0
 
Geert GOracle dbaCommented:
I believe you need to look at it again.

You are selecting everything from the same table 3 times, which is not necessary
0
 
JustAModCommented:
All,

This thread has been reviewed by at least one moderator, and the decision has been made to restart the Auto Close process.  

JustAMod
Community Support Moderator
0
 
Geert GOracle dbaCommented:
you must be using some obscure reasoning to come to that conclusion

it's probably not a logic reasoning ?
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.