I need to write an SQL statement that joins multiple tables, including one table twice, and sorts by counting a group .. I think
I am an amateur just trying to finish his last question of his assignment (it is past due at this point, just looking for understanding) I sat and shot attempts at this for almost 5 hours now across two days, and have had no success.

I have tried looking through all the different types of joins, couldn't get grouping to work (ever) and have had little luck with the sorting as well. I can do all of these things one at a time, but the difficulty here was getting all of these things to work in union. 

This is the question:

<em>Write a SQL query to retrieve a list that has (source city, source code, destination city, 
destination code, and number-of-flights) for all source-dest pairs with at least 2 flights. Order 
by the number_of_flights. Note that the “dest”, and “source” attributes in the “flights” table 
are both referenced to the “airportid” in the “airports” table.</em>


Here are the tables I have to work with (also came with about 3000 lines of dummy entries)

    create table airports (airportid char(3) primary key, city varchar(20));
    create table airlines (airlineid char(2) primary key, name varchar(20), hub char(3)                  references airports(airportid)); 
    create table customers (customerid char(10) primary key, name varchar(25), birthdate date, frequentflieron char(2) references airlines(airlineid));
    create table flights (flightid char(6) primary key, source char(3) references airports(airportid), dest char(3) references airports(airportid), airlineid char(2) references airlines(airlineid), local_departing_time date, local_arrival_time date);
    create table flown (flightid char(6) references flights(flightid), customerid char(10) references customers, flightdate date);

The first problem I ran in to was outputting airports.city twice in the same query but with different results. Not only that, but no matter what I tried when grouping I would always get the same result: "Not a GROUP BY expression". Normally I have fun trying to piece these together, but this has been frustrating. Help!
I need to write an SQL statement that joins multiple tables, including one table twice, and sorts by counting a group .. I think
===

I am an amateur just trying to finish his last question of his assignment (it is past due at this point, just looking for understanding) I sat and shot attempts at this for almost 5 hours now across two days, and have had no success.

I have tried looking through all the different types of joins, couldn't get grouping to work (ever) and have had little luck with the sorting as well. I can do all of these things one at a time, but the difficulty here was getting all of these things to work in union. 

This is the question:

<em>Write a SQL query to retrieve a list that has (source city, source code, destination city, 
destination code, and number-of-flights) for all source-dest pairs with at least 2 flights. Order 
by the number_of_flights. Note that the “dest”, and “source” attributes in the “flights” table 
are both referenced to the “airportid” in the “airports” table.</em>


Here are the tables I have to work with (also came with about 3000 lines of dummy entries)

    create table airports (airportid char(3) primary key, city varchar(20));
    create table airlines (airlineid char(2) primary key, name varchar(20), hub char(3)                  references airports(airportid)); 
    create table customers (customerid char(10) primary key, name varchar(25), birthdate date, frequentflieron char(2) references airlines(airlineid));
    create table flights (flightid char(6) primary key, source char(3) references airports(airportid), dest char(3) references airports(airportid), airlineid char(2) references airlines(airlineid), local_departing_time date, local_arrival_time date);
    create table flown (flightid char(6) references flights(flightid), customerid char(10) references customers, flightdate date);

The first problem I ran in to was outputting airports.city twice in the same query but with different results. Not only that, but no matter what I tried when grouping I would always get the same result: "Not a GROUP BY expression". Normally I have fun trying to piece these together, but this has been frustrating. Help!