CODEWITHSUNDEEP
mysqlsql
Yana
Yana

Reputation: 31

How can I write this SQL query?

I was wondering if I could get some help on this sql query. It´s from a test which I failed and now wanted to learn. I've spent a lot of time on it, watched tutorials on sql but I still don't get how it could be written..

There are 2 tables (flights, occupation_flights) with flight information and the task is to: Write a query that gives a list of flights for the next 7 days, and shows the flight, the number of available seats, number of occupied seats and a % of occupied seats.

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 show number of places 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, that means a person has not yet issued the ticket). TEST DATA I thought to get a number of available seats I should sum up cases when passenger is not empty per flight and date and extract that from PLACES. 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 make him calculate it in groups by flight and date at the same time, because there are flights with the same flight number but going on different dates. I also don't have access to tables, the test was on paper and to show that you know how to write sql queries. Would be really grateful for any insights! Many thanks in advance!

Upvotes: 3

Views: 2914

Answers (3)

KM11
KM11

Reputation: 757

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 Jeremy C. for building schema and Strawberry for COALLESCE function)

Result:

enter image description here

Upvotes: 0

Jeremy C.
Jeremy C.

Reputation: 2465

Mysql conform: 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

Upvotes: 1

blindpirate
blindpirate

Reputation: 114

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 result

enter image description here

PS. in my code, a MySql function IFNULL() is used, which should be replaced with other similar function in another database implement.

Upvotes: 0

Related Questions