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 :
But that query gives a total per city (rather than grouping the cities by state).
Thanks
-Al
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 :
Code:
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