I have the following N:N table (which stores the people boardings) in my Oracle Database (it is about an airport):
CREATE TABLE boardings(
Passport VARCHAR2(8),
Day DATE,
Flight VARCHAR2(8),
LuggageWeight NUMBER(4,2),
CONSTRAINT pk PRIMARY KEY(Passport, Day, Flight));
And I would like to make a query in order to see for each flight, which has been the day that has transported the highest amount of weight (keep in mind that a same flight, as RY-1234-VY for example, can make different travels in different days. I have been trying something like this, but it doesn't work:
SELECT Day, Flight
FROM test
GROUP BY Day, Flight
HAVING SUM(LuggageWeight) = (SELECT MAX(SUM(LuggageWeight))
FROM test
GROUP BY Day, Flight);
See Question&Answers more detail:os