Select with Nulls

My attached code works fine as long as the @DealID exists in the table

But if it doesn't, columns are shown with empty data row.
 Screenprint
What I would like returned is the DealID and nulls like this
 Screenprint2
Declare @DealID int
Set @DealID = 1988

Select	IsNull(Substring(afsSource,4,4),0) dealID, 
		Sum(Case When afsTransaction = 0 Then 1 Else 0 End) AS Zeroes,   
		Sum(Case When afsTransaction <> 0 Then 1 Else 0 End) AS NonZeroes  
From	proc_cfa.dbo.P_AvailableForSale
Where	substring(afsSource,4,4) = @DealID
Group By substring(afsSource,4,4)
order by Cast(substring(afsSource,4,4) as Int)

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
Missed ORDER BY
SELECT IsNull(Substring(A.afsSource,4,4),0), B.Zeroes, B.NonZeroes
FROM proc_cfa.dbo.P_AvailableForSale A
LEFT JOIN
(
	Select	afsSource, 
			Sum(Case When afsTransaction = 0 Then 1 Else 0 End) AS Zeroes,   
			Sum(Case When afsTransaction <> 0 Then 1 Else 0 End) AS NonZeroes  
	From	proc_cfa.dbo.P_AvailableForSale
	Where	substring(afsSource,4,4) = @DealID
	Group By substring(afsSource,4,4)
) B
ON A.afsSource = B.afsSource
order by Cast(substring(a.afsSource,4,4) as Int)

Open in new window


SELECT IsNull(Substring(A.afsSource,4,4),0), B.Zeroes, B.NonZeroes
FROM proc_cfa.dbo.P_AvailableForSale A
LEFT JOIN
(
	Select	IsNull(Substring(afsSource,4,4),0) dealID, 
			Sum(Case When afsTransaction = 0 Then 1 Else 0 End) AS Zeroes,   
			Sum(Case When afsTransaction <> 0 Then 1 Else 0 End) AS NonZeroes  
	From	proc_cfa.dbo.P_AvailableForSale
	Where	substring(afsSource,4,4) = @DealID
	Group By substring(afsSource,4,4)
) B
ON IsNull(Substring(a.afsSource,4,4),0) = B.dealID
order by Cast(substring(A.afsSource,4,4) as Int) 

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
Try this
SELECT IsNull(Substring(A.afsSource,4,4),0), B.Zeroes, B.NonZeroes
FROM proc_cfa.dbo.P_AvailableForSale A
LEFT JOIN
(
	Select	IsNull(Substring(afsSource,4,4),0) dealID, 
			Sum(Case When afsTransaction = 0 Then 1 Else 0 End) AS Zeroes,   
			Sum(Case When afsTransaction <> 0 Then 1 Else 0 End) AS NonZeroes  
	From	proc_cfa.dbo.P_AvailableForSale
	Where	substring(afsSource,4,4) = @DealID
	Group By substring(afsSource,4,4)
) B
ON IsNull(Substring(a.afsSource,4,4),0) = B.dealID

Open in new window

0
 
rushShahCommented:
try this,
Declare @DealID int
Set @DealID = 1988

IF EXISTS(SELECT 1 FROM proc_cfa.dbo.P_AvailableForSale WHERE substring(afsSource,4,4) = @DealID)
BEGIN
Select	IsNull(Substring(afsSource,4,4),0) dealID, 
		Sum(Case When afsTransaction = 0 Then 1 Else 0 End) AS Zeroes,   
		Sum(Case When afsTransaction <> 0 Then 1 Else 0 End) AS NonZeroes  
From	proc_cfa.dbo.P_AvailableForSale
Where	substring(afsSource,4,4) = @DealID
Group By substring(afsSource,4,4)
order by Cast(substring(afsSource,4,4) as Int)
END
ELSE
SELECT @DealID, NULL AS Zeroes, NULL AS NonZeroes

Open in new window

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Rajkumar GsSoftware EngineerCommented:
OR
SELECT IsNull(Substring(A.afsSource,4,4),0), B.Zeroes, B.NonZeroes
FROM proc_cfa.dbo.P_AvailableForSale A
LEFT JOIN
(
	Select	afsSource, 
			Sum(Case When afsTransaction = 0 Then 1 Else 0 End) AS Zeroes,   
			Sum(Case When afsTransaction <> 0 Then 1 Else 0 End) AS NonZeroes  
	From	proc_cfa.dbo.P_AvailableForSale
	Where	substring(afsSource,4,4) = @DealID
	Group By substring(afsSource,4,4)
) B
ON A.afsSource = B.afsSource

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
Ok folks
rushShah's was first one to work...seems a little clunky but what do I know?  I'm the one asking for help.

Points will be awarded shortly with no objections?
0
 
LowfatspreadCommented:
like this use left outer join

Declare @DealID int
Set @DealID = 1988

Select      x.dealID
       ,Sum(Case When afsTransaction = 0 Then 1 Else 0 End) AS Zeroes,  
       ,Sum(Case When afsTransaction <> 0 Then 1 Else 0 End) AS NonZeroes  
From (select @dealid as dealid) as x
left outer join      proc_cfa.dbo.P_AvailableForSale
 on substring(afsSource,4,4) = x.DealID
Group By x.dealid
order by 1
0
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks
0
 
Rajkumar GsSoftware EngineerCommented:
Glad I could help
Raj
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.