By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,767 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,767 IT Pros & Developers. It's quick & easy.

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

P: 9
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 | email@hotmail.com
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?
Apr 2 '09 #1
Share this Question
Share on Google+
5 Replies


ck9663
Expert 2.5K+
P: 2,878
What do you have so far?

---- CK
Apr 2 '09 #2

P: 9
I am only new to SQL & I have tried a million different things, nothing that even comes close to working :(
Apr 3 '09 #3

P: 9
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
Apr 3 '09 #4

Uncle Dickie
P: 67
You should be able to get what you are after with a LEFT JOIN rather than INNER JOIN

Something Like:

Expand|Select|Wrap|Line Numbers
  1. Select isnull(State,'Other'), Count(*) AS Count
  2. From tbl_fans
  3. LEFT JOIN
  4. tbl_postcodes ON tbl_postcodes.Postcode = tbl_fans.Fan_Postcode
  5.  
  6. WHERE Code_Prefix = 'WHATEVER'
  7.  
  8. Group By State
Apr 3 '09 #5

P: 9
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
Apr 3 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.