How can I write this SQL query?
I was wondering if I could help with this sql query. This is from a test I failed and now want to study. I spent a lot of time looking at the sql tutorials, but I still don't understand how this could be written.
There are 2 tables (flights, flight_classes) with flight information and the task is as follows: Write a query that lists flights for the next 7 days and shows the flight, the number of seats available, the number of seats occupied and the% of seats occupied.
FLIGHTS:
FLIGHT ID_COMPANY DATE ORIGIN DESTINATION PLACES
1003 D3 20/01/2006 MADRID LONDRES 40
1007 AF 20/01/2006 PARIS MALAGA 12
1003 15 30/01/2006 MADRID LONDRES 20
1007 AF 30/01/2006 PARIS MALAGA 17
(PLACES shows the number of seats offered on each flight)
OCCUPATION_FLIGHTS:
FLIGHT ID_COMPANY DATE PASSENGER SEAT
1003 IB 20/01/2006 07345128H 01V
1003 IB 20/01/2006 1013456213 01P
1003 IB 20/01/2006 08124356C 02V
1003 IB 20/01/2006 57176355K 02P
1007 AF 20/01/2006 27365138A
1003 IB 30/01/2006 734512811 01V
1003 IB 30/01/2006 1013456213 02V
1003 IB 30/01/2006 57176355K
(When SEAT is empty, it means that the person has not yet issued a ticket). TEST DATA I was thinking of getting a few available seats, I have to take stock when the passenger is not empty for the flight and date and get him out of the SEAT. So I started with smth like:
SELECT
f.flight,
f.places - (SELECT (CASE WHEN of.passanger IS NOT NULL THEN 1 ELSE 0 END)
FROM occupation_flights AS of GROUP BY of.flight, of.date) AS available,
f.places - (SELECT (CASE WHEN of.passanger IS NULL THEN 1 ELSE 0 END) FROM occupation_flights AS of GROUP BY of.flight, of.date) AS occupied,
100.0 * ((f.places - (SELECT (CASE WHEN of.passanger IS NOT NULL THEN 1 ELSE 0 END) FROM occupation_flights AS of GROUP BY of.flight, of.date)) / f.places AS %occupied
FROM flights AS f
JOIN occupation_flights AS of ON f.flight=of.flight
WHERE f.date BETWEEN DateAdd(DD,-7,GETDATE() ) and GETDATE()
GROUP BY f.flight
but it's not finished because I really don't understand how to get it to calculate it in groups of flight and date at the same time, because there are flights with the same flight number but on different dates. I also don't have access to tables, the test was on paper and showed that you know how to write sql queries. Would really appreciate any ideas! Thank you very much in advance!
source to share
Mysql Compliance: SQLFIDDLE
SELECT f.flight,f.mydate,f.places - count(of.seat) as available_seats,
count(of.seat) as occupied,
count(of.seat)/f.places * 100 as percentage_occupied
FROM flights f
JOIN occupation_flights of
ON f.flight = of.flight AND f.mydate = of.mydate
WHERE of.mydate BETWEEN DATE_ADD(CURDATE(), INTERVAL -7 DAY)AND CURDATE()
group by f.flight, f.mydate
source to share
select t1.flight,
t1.date,
t1.places,
t3.c,
t1.places-IFNULL(t3.c, 0) as empty
from flights t1
left join(
select count(t2.seat) as c, t2.flight, t2.date
from occupation_flights t2
where seat is not null
group by flight, date
) t3
on t1.flight= t3.flight and t1.date= t3.date
and here is the result
PS. my code uses the MySql IFNULL () function, which should be replaced with another similar function in another database tool.
source to share
SELECT
f.flight, f.mydate, f.id_company, origin, destination,
places, (places - COALESCE(t.ocupped_seats, 0)) as available_places,
IFNULL(t.ocupped_seats, 0) as ocupped_places,
CONCAT(ROUND(COALESCE(places/COALESCE(t.ocupped_seats, 0), 0), 2), '%') as percentage
FROM
flights as f
LEFT JOIN
(SELECT
flight, mydate,id_company, COUNT(seat) as ocupped_seats
FROM
occupation_flights
WHERE
seat IS NOT NULL
GROUP BY
flight, mydate) as t
ON
f.flight = t.flight AND f.mydate LIKE t.mydate
WHERE
f.mydate BETWEEN CURDATE() AND CURDATE()+7
DEMO (Thanks to Jeremy C. for building the circuit and Strawberry for the function COALLESCE
)
Result:
source to share