How to show zeros in the results of a select statement

[ausmoran]

In the following select statement, some of the GROUP_CODES between '100' and '581' have a resulting COUNT of ZERO. Unfortunately, when the result is zero, those GROUP_DESCRIPTION simply do not show up in the result set. Is there a simple way to show the '0' count instead of not having the GROUP_DESICRIPTION show up at all? Here is the code that I'm using (thanks in advance for your kind help):
--------------------

SELECT List_Code.Group_Description,
COUNT (Distinct Member.Member_Number) 'Count',
convert(decimal(5, 2), null) as Percentage
INTO #count
FROM List_Maker_File, List_Code,member,member_association
WHERE List_Group_Code Between '100' and '581'
AND List_Code.Group_Code=List_Maker_File.List_Group_Code
AND Member.Member_Number=List_Maker_File.Member_Office_Number And List_Maker_File.Member_Office='M'
AND Member.Member_Number=Member_Association.member_number
AND Member_Association.Status='a'
AND Member_Association.Primary_Indicator='p'
Group By Group_Description
Order By Group_Description

update #count set Percentage = 100.0 * [count]/(select sum([count]) from #count)
select * from #count
drop table #count

------------------------

- Austin

 

[ausmoran]

Just for clarification purposes, here is a partial printout of the result set. Unfortunately, if any groups do not contain members, they do not show up in the result set. I need to find a way to show ALL of the Group_Descriptions, even when the COUNT is zero.


Group_Description Count Perecentage

Account or Sales Rep 57 1.23
Accountant/CPA 6 .13
Acquisition 2 .04
Appraisal 82 1.77
Appraiser 18 .39

 

[amrita418]

Try this:

SELECT    	List_Code.Group_Description,
     		sum(case when b.Member_Number is null then 0 else 1 end) 'Count',
    		CONVERT(DECIMAL(5, 2), null) as Percentage
--INTO        #count
FROM 		List_Code
		left outer join
		List_Maker_File
		on (List_Maker_File.List_Group_Code = List_Code.list_Group_Code)
		left outer join  
		(select distinct member.member_number 'member_number'
		from member inner join member_association
		on (Member_Association.member_number = Member.Member_Number
		    AND Member_Association.Status='a'
    		    AND Member_Association.Primary_Indicator='p')) b
		on (List_Maker_File.Member_Office = b.Member_Number)
WHERE 		List_Code.List_Group_Code Between '100' and '581'
		And List_Maker_File.Member_Office='M'
Group By 	List_Code.Group_Description
Order By 	List_Code.Group_Description

PS: Some column names might not match with your tables so you will need to update them before running the query.

Regards,
AA

 

[ausmoran]

Thanks again, AA, but I am coming up with a conversion error that I am unable to solve. Here is the modified code:
---------------------------

SELECT        List_Code.Group_Description,
             sum(case when b.Member_Number is null then 0 else 1 end) 'Count',
            CONVERT(DECIMAL(5, 2), null) as Percentage
--INTO        #count
FROM         List_Code
        left outer join
        List_Maker_File
        on (List_Maker_File.List_Group_Code = List_Code.Group_Code)
        left outer join  
        (select distinct member.member_number 'member_number'
        from member inner join member_association
        on (Member_Association.member_number = Member.Member_Number
            AND Member_Association.Status='a'
                AND Member_Association.Primary_Indicator='p')) b
        on (List_Maker_File.Member_Office = b.Member_Number)
WHERE         List_Code.Group_Code Between '100' and '581'
        And List_Maker_File.Member_Office='M'
Group By     List_Code.Group_Description
Order By     List_Code.Group_Description

update #count set Percentage = 100.0 * [count]/(select sum([count]) from #count)
select * from #count
drop table #count

And the error that is coming up is:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'M' to a column of data type int.

-----------------
THANKS again for your continued assistance. You've gone WAY beyond what most anyone else would do. I greatly appreciate it.

- Austin

 

[SQLBill]

List_Maker_File.Member_Office='M'

What datatype is Member_Office?

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

If you double-click on the error message, QA will highlight the line with the error and my educated guess is that this is the line with the problem:

on (List_Maker_File.Member_Office = b.Member_Number)

Member_Office seems to be a character string datatype (that's why you are evaluating it to 'M'), however Member_Number is probably INTEGER datatype (that's why it's called Number, right?).

-SQLBill

Posting advice: FAQ481-4875

 

[ausmoran]

I wish it were that simple, Bill! When I dbl-click on the error message, the only item that is highlighted is
SELECT List_Code.Group_Description,


Not sure where to go from here! Member_Office is a VARCHAR data type.

Thanks for your help, Bill

 

[amrita418]

What is the datatype of member_number?

Regards,
AA

 

[ausmoran]

Member.member_number is an INT datatype. THANKS

 

[amrita418]

Thats your problem right there!!

It is failing on this condition.

on (List_Maker_File.Member_Office =  b.Member_Number

Change it to

on (List_Maker_File.Member_Office = convert(varchar, b.Member_Number)

I am still not sure if this condition is still valid?

Regards,
AA

 

[ausmoran]

AA, you are worth your weight in GOLD!!!!! I have been through this code line by line fifty times and just couldn't see it. But when you pointed out that I needed to change the datatype of list_maker_File.Member_Office it made me realize that we had the wrong field. The field should have been List_Maker_File.Member_Office_Number (which is an INT datatype). That seems to have solved it! I feel that you have been a great teached this week, AA! What a gift you continue to provide to me.

I am most grateful! - Austin

 

[ausmoran]

URGGHHHHHH, I'm sorry to return! I've got to figure out a way to get the DISTINCT keyword in for the MEMBER.MEMBER_NUMBER in the first part of the SELECT statement so that I eliminate duplicates. I'm not sure how to do it, now that we have included the CASE statement along with it. Can you help me out yet again? THANKS once again!!!!!! - Austin

SELECT        List_Code.Group_Description,
             sum(case when b.Member_Number is null then 0 else 1 end) 'Count',
            CONVERT(DECIMAL(5, 2), null) as Percentage
INTO        #count
FROM         List_Code
        left outer join
        List_Maker_File
        on (List_Maker_File.List_Group_Code = List_Code.Group_Code)
        
left outer join  
        (select distinct member.member_number 'member_number'
        from member inner join member_association
        on (Member_Association.member_number = Member.Member_Number
            AND Member_Association.Status='a'
                AND Member_Association.Primary_Indicator='p')) b
        on (List_Maker_File.Member_Office_Number = b.Member_Number)
WHERE         List_Code.Group_Code Between '100' and '581'
	     And List_Maker_File.Member_Office='M'
Group By     List_Code.Group_Description
Order By     List_Code.Group_Description

update #count set Percentage = 100.0 * [count]/(select sum([count]) from #count)
select * from #count
drop table #count

 

[amrita418]

which tables have the 1:n relationship?

 

[amrita418]

Did you try replacing

sum(case when b.Member_Number is null then 0 else 1 end) 'Count'

with

COUNT (Distinct Member.Member_Number) 'Count

Regards,
AA

 

[ausmoran]

When you were helping me out yesterday, AA, we inserted the DISTINCT keyword into the select statement as follows


SELECT List_Code.Group_Description,
COUNT (Distinct Member.Member_Number) 'Count',
convert(decimal(5, 2), null) as Percentage
INTO #count

...and that worked fine for eliminating duplicates. But now that we've had to add the CASE statement, I am unsure how to add the DISTINCT keyword.

Just to give you an example of the problem. The code that I have listed above in the color RED produces a count of 57 Sales Reps. But the NEW modified code produces a count of 244 Sales Reps. It is only due to the lack of the DISTINCT keyword. THANKS AGAIN!

 

[amrita418]

Replace the line with old one and let us know if it works?

 

[ausmoran]

What I was trying to do, was use that left outer joing, so that I could show ALL Group_Descriptions, even when they had a count of zero. If I use the old line of code, I get the correct count, but I no longer get ALL of the Group_Descriptions.

 

[amrita418]

oh, yea now I remember...

Can you restate which table has the dups rows?

 

[ausmoran]

I believe it is the MEMBER table. At least, yesterday when you inserted the DISTINCT keyword in the following code, the count was accurate (though we didn't list all of the Group_descriptions because we were NOT using a left outer join:

--- ORIGINAL CODE WITHOUT LEFT OUTER JOIN ----
SELECT	List_Code.Group_Description,
	 COUNT (Distinct Member.Member_Number) 'Count',
	convert(decimal(5, 2), null) as Percentage
INTO		#count
FROM List_Maker_File, List_Code,member,member_association
WHERE List_Group_Code Between '100' and '581'
	AND List_Code.Group_Code=List_Maker_File.List_Group_Code 
	AND Member.Member_Number=List_Maker_File.Member_Office_Number And List_Maker_File.Member_Office='M'
	AND Member.Member_Number=Member_Association.member_number 
	AND Member_Association.Status='a'
	AND Member_Association.Primary_Indicator='p'
Group By Group_Description
Order By Group_Description

update #count set Percentage = 100.0 * [count]/(select sum([count]) from #count)
select * from #count
drop table #count

 

[amrita418]

This select should have taken care of that.

(select    distinct 
           member.member_number 'member_number'
from       member inner join member_association
           on (Member_Association.member_number = Member.Member_Number
            AND Member_Association.Status='a'
            AND member_Association.Primary_Indicator='p'))

Run this piece by itself, make sure you get the right results, then add list_File_Maker table join and see if you get the right results, then finally add the list_code table. I do not have sample data to test so you need to make debug it.

Forget the previous code, the code we finalized this morning should work. I am sure its just a silly thing that we are missing.

Keep me posted.
Regards,
AA