Count by area type query (1 Viewer)

[bigal.nz]

Hello,

I want to create a query to sum by state - the data doesn't have state, it has city (in this data there is only a few cities per state).

So I presume I need a table to Map city to state?

The data looks like:

TABLE MAS:
ID | COLOR | CITY |
0 | Blue | San Deigo |
1 | Blue | Las Vegas |
2 | Blue | Los Angeles |
3 | Red | Las Vegas |
4 | Red | Reno |
5 | Orange | San Francisco |

The query wants to give result like so:

Color | Total | State
Blue | 2 | CA
Blue | 1 | NV
Red | 2 | NV
Orange | 1 | CA

Assuming I do need a state/city table I would create it like so:

ID | State | City
0 | CA | Los Angeles
1 | CA | San Deigo
2 | NV | Las Vegas
3 | NV | Reno
4 | CA | San Francisco

I got as far as :

SELECT MAS.color, MAS.city, Count(*) AS Expr1
FROM MAS
GROUP BY MAS.color, MAS.city;

But that query gives a total per city (rather than grouping the cities by state).

Thanks

-Al

 

[jdraw]

What do you want as final format/result?

SELECT MAS.COLOR, CityState.State, Count(MAS.COLOR) AS CountOfCOLOR
FROM MAS INNER JOIN CityState ON MAS.CITY = CityState.City
GROUP BY MAS.COLOR, CityState.State;

COLOR State CountOfCOLOR
Blue CA 2
Blue NV 1
Orange CA 1
Red NV 2

 

[bigal.nz]

What do you want as final format/result?

SELECT MAS.COLOR, CityState.State, Count(MAS.COLOR) AS CountOfCOLOR
FROM MAS INNER JOIN CityState ON MAS.CITY = CityState.City
GROUP BY MAS.COLOR, CityState.State;

COLOR State CountOfCOLOR
Blue CA 2
Blue NV 1
Orange CA 1
Red NV 2

What you have done looks like exactly what I want - I assume its based on a table mapping city to state called CityState?

 

[arnelgp]

You need to add the state/city id to table MAS.

 

[Dystonia]

If you only have a few cities, use could the SWITCH function ?

SELECT Switch(City='San Diego','CA',City='Las Vegas','NV') as State

...not recommended though if you have thousands of cities and 50 states

 

[jdraw]

I used a statecity table

 

[bigal.nz]

What do you want as final format/result?

SELECT MAS.COLOR, CityState.State, Count(MAS.COLOR) AS CountOfCOLOR
FROM MAS INNER JOIN CityState ON MAS.CITY = CityState.City
GROUP BY MAS.COLOR, CityState.State;

COLOR State CountOfCOLOR
Blue CA 2
Blue NV 1
Orange CA 1
Red NV 2

Perfect. Thank you.