How do I group US states by region in MySQL?

Question

I have a MySQL table of distributor sales in the US. It contains the postal mailing address for each location including the state. I've been asked to produce a report which would list the distributors by region such as Pacific, Mountain, West North Central, East North Central, Middle Atlantic, New England, West South Central, East South Central, South Atlantic, and AK & HI as AK_HI.

The reports needs to look like this:

Region        State Average_Sales_by_state number_of_distributors
------        ----- ---------------------- ----------------------
Pacific       WA    $1234                  10
              OR    $4321                  20
              CA    $9876                  40
New England   ME    $1212                  15
              MA    $2222                  12
...

How do I approach this in MySQL so that when I do a query to produce the reports it comes together in an elegant fashion? Also, it needs to be sorted in descending order by Average_Sales_by_state column (which I didn't do in my above example).

I'm thinking I should create a new MySQL table called US_Regions and it would have a record layout with region & state, and there would be a row per state. If I go that route, what would the query look like to produce a report such as the one above? Thanks!

Here is the MySQL US_Regions table:

+----+--------------------+-------+
| id | Region             | State |
+----+--------------------+-------+
|  1 | Pacific            | WA    |
|  2 | Pacific            | OR    |
|  3 | Pacific            | CA    |
|  4 | Mountain           | MT    |
|  5 | Mountain           | ID    |
|  6 | Mountain           | WY    |
|  7 | Mountain           | NV    |
|  8 | Mountain           | UT    |
|  9 | Mountain           | CO    |
| 10 | Mountain           | AZ    |
| 11 | Mountain           | NM    |
| 12 | West North Central | ND    |
| 13 | West North Central | MN    |
| 14 | West North Central | SD    |
| 15 | West North Central | NE    |
| 16 | West North Central | IA    |
| 17 | West North Central | KS    |
| 18 | West North Central | MO    |
| 19 | East North Central | WI    |
| 20 | East North Central | IL    |
| 21 | East North Central | MI    |
| 22 | East North Central | IN    |
| 23 | East North Central | OH    |
| 24 | Middle Atlantic    | PA    |
| 25 | Middle Atlantic    | NY    |
| 26 | Middle Atlantic    | CT    |
| 27 | Middle Atlantic    | NJ    |
| 28 | New England        | NH    |
| 29 | New England        | VT    |
| 30 | New England        | ME    |
| 31 | New England        | MA    |
| 32 | New England        | RI    |
| 33 | West South Central | OK    |
| 34 | West South Central | AR    |
| 35 | West South Central | TX    |
| 36 | West South Central | LA    |
| 37 | East South Central | KY    |
| 38 | East South Central | TN    |
| 39 | East South Central | MS    |
| 40 | East South Central | AL    |
| 41 | South Atlantic     | WV    |
| 42 | South Atlantic     | VA    |
| 43 | South Atlantic     | NC    |
| 44 | South Atlantic     | SC    |
| 45 | South Atlantic     | GA    |
| 46 | South Atlantic     | FL    |
| 47 | South Atlantic     | DE    |
| 48 | South Atlantic     | MD    |
| 49 | South Atlantic     | DC    |
| 50 | Alaska-Hawaii      | AK    |
| 51 | Alaska-Hawaii      | HI    |
+----+--------------------+-------+

Kickstart Kickstart · Accepted Answer · 2013-01-29T11:04:00

Very big guesses here on your table descriptions, but something like this (untested). Hopefully this gives you the idea using a table of regions with one row per state.

SELECT b.Region, a.State, AVG(SaleValue) AS Average_Sales_by_state, COUNT(b.DistributorId)
FROM Sales a
INNER JOIN Distributors b ON a.DistributorId = b.DistributorId
INNER JOIN US_Regions c ON b.State = c.State
GROUP BY b.Region, a.State
ORDER BY b.Region, Average_Sales_by_state DESC

How do I group US states by region in MySQL?

1 Answers