SQL flight 2, Sql Server

SQL flight 2

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

CREATE TABLE flights_table(DEP CHAR(3), ARR CHAR(3), COST INT)
INSERT INTO flights_table VALUES ('SEA', 'CHG', 10);
INSERT INTO flights_table VALUES ('CHG', 'SEA', 20);
INSERT INTO flights_table VALUES ('SEA', 'SFO', 30);
INSERT INTO flights_table VALUES ('SEA', 'LSA', 40);
INSERT INTO flights_table VALUES ('SEA', 'SJO', 50);
INSERT INTO flights_table VALUES ('SFO', 'CHG', 60);
INSERT INTO flights_table VALUES ('SFO', 'SEA', 70);
INSERT INTO flights_table VALUES ('BOS', 'SEA', 80);
INSERT INTO flights_table VALUES ('NYC', 'CHG', 90);
INSERT INTO flights_table VALUES ('NYC', 'SEA', 100);

SELECT DISTINCT CASE WHEN dep < arr THEN dep else arr END AS col1,
    CASE WHEN dep < arr THEN arr ELSE dep END AS col2,
    COST
FROM flights_table

SELECT CONCAT(col1, '-', col2) AS route, SUM(cost) AS cost FROM(
SELECT DISTINCT CASE WHEN dep < arr THEN dep else arr END AS col1,
    CASE WHEN dep < arr THEN arr ELSE dep END AS col2,
    COST
FROM flights_table)t
GROUP BY CONCAT(col1, '-', col2)
ORDER BY cost DESC


run \| edit \| history \| help	0