SELECT
    Name,
    COUNT(Flight.FlightNumber) AS TotalFlights,
    FORMAT(SUM(Credits), "$#,###.#0") AS [TotalCredits],
    FORMAT(AVG(Credits), "$#,###.#0") AS [AverageCredits]
FROM (
 
    SELECT
        Passenger.Name,
        Flight.FlightNumber,
        FrequentFlier.PremierStatus,
        IIf(FrequentFlier.PremierStatus = 1, 15,
            IIf(FrequentFlier.PremierStatus = 2, 30,
            IIf(FrequentFlier.PremierStatus = 3, 45, 0))) AS Credits
    FROM Flight
    INNER JOIN (
        Manifest
        INNER JOIN (
            Passenger
            INNER JOIN FrequentFlier ON Passenger.FFNumber = FrequentFlier.FFNumber
        ) ON Passenger.PassengerNumber = Manifest.PassengerNumber
    ) ON Flight.FlightNumber = Manifest.FlightNumber
    WHERE MONTH(FlightDate) IN (6, 7, 8)
    GROUP BY Passenger.Name, Flight.FlightNumber, FrequentFlier.PremierStatus


    UNION ALL


    SELECT
        Passenger.Name,
        Flight.FlightNumber,
        FrequentFlier.PremierStatus,
        IIf(FrequentFlier.PremierStatus = 1, 25,
            IIf(FrequentFlier.PremierStatus = 2, 50,
            IIf(FrequentFlier.PremierStatus = 3, 75, 0))) AS Credits
    FROM Flight
    INNER JOIN (
        Manifest
        INNER JOIN (
            Passenger
            INNER JOIN FrequentFlier ON Passenger.FFNumber = FrequentFlier.FFNumber
        ) ON Passenger.PassengerNumber = Manifest.PassengerNumber
    ) ON Flight.FlightNumber = Manifest.FlightNumber
    WHERE MONTH(FlightDate) NOT IN (6, 7, 8)
    GROUP BY Passenger.Name, Flight.FlightNumber, FrequentFlier.PremierStatus
) AS CreditsData


GROUP BY Name
HAVING Count(Flight.FlightNumber) >= 5
ORDER BY SUM(Credits) DESC ,  Avg(Credits) DESC