SQL-Join Task + Where(Having) + Group By
Display a list of passengers who have made at least 1 flight, sorted by the number of flights (in descending order).
Fields in the result table: name, count
Link to the task - https://sql-academy.org/ru/trainer/tasks/16
Solution attempt
SELECT name, COUNT(trip) as count
FROM
Passenger
JOIN Pass_in_trip on Passenger.id = Pass_in_trip.passenger
Where
trip >= 1
GROUP
BY name
ORDER BY count DESC
I don't understand what the error is. The system says that the solution is not correct, although it seems to me that everything is correct.
5 answers
The correct solution is a simple query (on db fiddle):
SELECT name, COUNT(*) as count
FROM Passenger
JOIN Pass_in_trip on Pass_in_trip.passenger = Passenger.id
JOIN Trip on Trip.id = Pass_in_trip.trip and Trip.time_out <= now()
GROUP BY Passenger.id, Passenger.name
HAVING COUNT(*) >= 1
ORDER BY count DESC
It is not correct to group only by the name of the passenger, since different people may have the same names, it is necessary by the ID and name.
Where
trip >= 1
Pass_in_trip.trip corresponds to the field Trip.id, i.e. you check here that id of a certain flight in the flight table >= 1, and not at all the number of flights of a particular passenger, as you think.
This is the correct request. No need to join the Trip{[2] table]}
SELECT DISTINCT name, COUNT(Pass_in_trip.trip) as count FROM Passenger
LEFT JOIN Pass_in_trip ON Passenger.id = Pass_in_trip.passenger
GROUP BY Passenger.id HAVING count >= "1"
ORDER BY count DESC;I got a slightly more concise answer:
SELECT name, COUNT(name) as count from Passenger
INNER JOIN Pass_in_trip ON Passenger.id = Pass_in_trip.passenger
GROUP BY Passenger.id
ORDER BY count DESC
In the table Pass_in_trip there are only those passengers who had at least 1 flight, so you do not need to do additional checks and join other tables.
SELECT name, COUNT(name) as count FROM Passenger, Pass_in_trip
WHERE Pass_in_trip.passenger= Passenger.id
GROUP BY name HAVING COUNT(count) >= 1
ORDER BY count DESC