I have a mySQL database table "orders" that includes each customer's zip code. I have another table, "zipcodes", that contains all the US zip codes and their corresponding cities/towns.
I want to generate a report on order data, along the lines of:
City # of Orders
Chicago 5
San Francisco 4
Cleveland 3
Smalltown 1
etc.
In the "orders" table, I might have separate orders from zip codes 60601 and 60614, but both correspond to "Chicago" in the zip codes table, and I would want my report to lump them together as such (I'm concerned that customers may not have typed in city names in common fashion, or I'd do it that way). Can I generate this sort of report with a single SQL statement or would I have to utilize arrays? If arrays, would I need to do some sort of check where (as I'm looping through), if the city is not already in the orders array, add a new entry, and, if it is, then increment a separate count value? What type of array would that be?
Any help would be appreciated. Thanks.
SQL or Array for City Names/Zip Codes?
Moderator: General Moderators
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: SQL or Array for City Names/Zip Codes?
Code: Select all
SELECT COUNT(*) AS numorders, zip_codes_table.city_name FROM orders
INNER JOIN zip_codes_table ON orders.zipcode = zip_codes_table.zipcode
GROUP BY zip_codes_table.city_nameRe: SQL or Array for City Names/Zip Codes?
That is outstanding--thank you so much for your help.
If you don't mind, I have one follow-up question.....In orders.zipcode, we'll have cases where the Chicago zips are 60601, 60614, and 60614-1234 (example). The zip_codes_table.zipcode, however, only has five digits (e.g. 60601 or 60614), so what would be the best approach for getting the "60614-1234" cases included as part of the Chicago counts, rather than as separate counts (b/c there's not a strict equality with zip_codes_table)? Is it possible to use LIKE or REGEXP on generic column names, rather than quoting some sort of string withinthe SQL? And would I have to reverse the order of your ON statement, something along the lines of:
INNER JOIN zip_codes_table ON zip_codes_table.zipcode = orders.zipcode REGEXP "^"
Any help would be appreciated. Thanks again.
If you don't mind, I have one follow-up question.....In orders.zipcode, we'll have cases where the Chicago zips are 60601, 60614, and 60614-1234 (example). The zip_codes_table.zipcode, however, only has five digits (e.g. 60601 or 60614), so what would be the best approach for getting the "60614-1234" cases included as part of the Chicago counts, rather than as separate counts (b/c there's not a strict equality with zip_codes_table)? Is it possible to use LIKE or REGEXP on generic column names, rather than quoting some sort of string withinthe SQL? And would I have to reverse the order of your ON statement, something along the lines of:
INNER JOIN zip_codes_table ON zip_codes_table.zipcode = orders.zipcode REGEXP "^"
Any help would be appreciated. Thanks again.