CODEWITHSUNDEEP
mysqlchartsgoogle-visualization
user7668201
user7668201

Reputation:

How to set all 50 states to a country code as 'US'

Okay so I'm using Google Charts API to create a map that displays sales based on location. Some code in my chart are countries so France is displayed in my CSV file as FR. However, the API ONLY does Countries so my data in the file that are states such as NC, CA, NY etc... need to be stored as US. Would a case statement for each state be the best way to go? 

States        I need these states to be set equal to 'US'
StateID
-------
AL
CA
HI
NY
etc...

Upvotes: 1

Views: 59

Answers (1)

WhiteHat
WhiteHat

Reputation: 61275

try this sql... 

SELECT
  CASE WHEN
    CUST_STATE_CD in ('AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'PR', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY')
  THEN
    'US'
  ELSE
    CUST_STATE_CD
  END as state,
  count(CUST_NM) as totalCust
FROM
  sales_filev1
GROUP BY
  CASE WHEN
    CUST_STATE_CD in ('AK', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 'OH', 'OK', 'OR', 'PA', 'PR', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VA', 'WA', 'WV', 'WI', 'WY')
  THEN
    'US'
  ELSE
    CUST_STATE_CD
  END

Upvotes: 0

Related Questions