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]

I seem to be getting hung up when I get to the "b" just before adding the list_file_maker table. I'm sorry to be so limited here and to be taking up all of your time. You are so generour. I really appreciate it and feel like an idiot.

 

[amrita418]

Ok, lets take this step by step.

First did you get the right results when you the above select stmt?

If yes then
Run something like this

select     List_Maker_file.Member_office_Number, 
           b.Member_Office_Number
from       List_Maker_File
           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)
order by List_Maker_File.Member_Number

If you see duplicate entries then you know that is the problem.

Regards,
AA

 

[ausmoran]

I do not understand the use of the "b" following the double "))" (parenthesis). When I run that piece of code, I get two errors as follows:


Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Member_Office_Number'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Member_Number'.


These errors don't make sense to me, since these ARE valid columns. I'm afraid I'm getting lost here. Thanks again for your continued support, AA. I hope I'm not frustrating you in the process.

 

[amrita418]

Would be helpful if you post the code you are trying to run.

Remove the extra ')' and b before you run the that stmt.

 

[ausmoran]

Thanks, AA! here's what I'm trying to run, as you posted it:

select     List_Maker_file.Member_office_Number,
           b.Member_Office_Number
from       List_Maker_File
           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)
order by List_Maker_File.Member_Number

Would you be kind enough to explain the theory behind the use of the "b"? In looking at the first line, I see that the initial select statement actually seems to be pulling the same field twice; List_Maker_file.Member_office_Number (if 'b' = List_Maker_file). I hate to impose more than I already have (and I know I have already monopolized way too much of your time) but I would really appreciate it if you could sort of give me a thumbnail chronology of how this code works (if that's not asking too much). I just don't understand how the stuff in parentheses before the "b" works. THANKS ever so much.

 

[ausmoran]

I get the following errors when running the code:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'from'.
Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'on'.

 

[amrita418]

ok here it goes:

THe code below should display distinct member_numbers.

(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')

In the next step we are creating an inline view that will be used by the subsequent joins. I just named the view as B, you can name it whatever you want.

In the next step we are trying to join list_Maker_File member_number with the values from the view.

If there is only one entry per member_office_number in List_File_Maker table then we should see only one entry per ID.

Since we are doing a distinct on the Member_Number column in the view we are guaranteed only one entry exists per ID.

Since you say that you are getting multiple entries per Group_Description either the member_number in list_file_maker is duplicated or list group code is duplicated.

Getting back to running the last stmt, I accidentally interchanged the columns.
Try this:

select     a.Member_office_Number,
           b.Member_Number
from       List_Maker_File a
           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 (a.Member_Office_Number = b.Member_Number)
order by  a.Member_Office_Number

Regards,
AA