dimanche 31 mai 2015

Trouble joining and filtering data in sqlite

I know this is a simple question, and I tried to find a solution but with no luck. I'm a sqlite newb and am having trouble performing this query. First, the .schema:

CREATE TABLE cities (name text, state text);
CREATE TABLE weather (city text, year int, warm_month text, cold_month text, average_high int);

I have a table of city name / state values, and then a weather table with the columns listed above. I need my query to find the mean of the average high temperatures for all of the cities within a state.

My query is as follows:

select city, avg(average_high) from weather
  ...> left outer join cities
  ...> on name = city
  ...> group by state;

This actually displays the correct average for each state, but it displays a city name rather than the state name. I know that I ask for city in my select statement, and I never select state anywhere... I guess I need an additional select statement but I don't know the syntax.

Thanks for helping me resolve this trivial question!

Aucun commentaire:

Enregistrer un commentaire