Getting a count on one table using data from another table.

[new2sql]

Firstly, thanks in advance for any assistance, it is much appriciated.

I have two tables, structure as follows:

TBL_FANS

Column | Example
--------------------------------------
Fan_ID | 256
Code_Prefix | ABC
Email | [e-mail]
Postcode | 2015
--------------------------------------

TBL_POSTCODES

Column | Example
--------------------------------------
Postcode | 2015
State | NSW
--------------------------------------

I need to get a count of what state all of the fans are in so that I can use this data in a charting package to show the location of the fans. The data will need to be in this format

State | Count
--------------------------------------
NSW | 125
QLD | 75
VIC | 100
Other | 20

If the query does not find a match to the Fans Postcode in TBL_POSTCODES then it should be added to a new row named other.

Any ideas on how I go about accomplishing this?

[ck9663]

What do you have so far?

---- CK

[new2sql]

I am only new to SQL & I have tried a million different things, nothing that even comes close to working :(

[new2sql]

Ok I have made some progress with the following query:
----
Select State, Count(*) AS Count
From tbl_postcodes
INNER JOIN
tbl_fans ON tbl_postcodes.Postcode = tbl_fans.Fan_Postcode

WHERE Code_Prefix = 'WHATEVER'

Group By State
----

This 'seems' to work, however I need to add the postcodes that do not return a match to a count called 'Other'. as per below:

State | Count
--------------------------------------
NSW | 125
QLD | 75
VIC | 100
*Other | 20

[Uncle Dickie]

You should be able to get what you are after with a LEFT JOIN rather than INNER JOIN

Something Like:

Select isnull(State,'Other'), Count(*) AS Count
From tbl_fans
LEFT JOIN
tbl_postcodes ON tbl_postcodes.Postcode = tbl_fans.Fan_Postcode
 
WHERE Code_Prefix = 'WHATEVER'
 
Group By State

[new2sql]

Thanks for the info, I have tested the above and cannot seem to get it to work.

If the postcode field contains an overseas Postcode/ZIP (say 90210) for example this record is not returned in the count, even though there is definetely no match in tbl_postcodes. The rest of the count remains correct however.

Sample Output:

NSW 7
QLD 20
SA 2
VIC 2
WA 1