Count function in select query 1

[Aoife]

I have a very simple select query which counts and displays the occurrences of various result codes in a customer table. The data is output as follows:

Result Code Count of Result Code

LAO 10
LOT 8

The customer table is linked to a result code table. There are a number of codes for which there are no occurrences in the customer table. I need to have these displayed also in the output table with the count as zero. How do I go about doing this?

Thanks in advance

 

[jhall156]

You need to use an outer join.
What this does is allow you to in effect say 'give me all the rows from this table and only the rows from the other table that satisfy this join condition'


SELECT a.resultcode, COUNT(b.resultcode) AS resultcount
FROM resultcodetable a LEFT OUTER JOIN customertable ON
a.resultcode = b.resultcode
GROUP BY a.resultcode

This is to say 'give me all the resultcodes from the result code table (the group by says to only give one row per resultcode) and the counts of those resultcodes in the customer table where the count codes in the 2 tables are equal'

I started out with nothing, and I still have most of it.

 

[Aoife]

That did it, thanks a lot for your help!