I want to find the cinema with the max total number of tickets sold:

The number of tickets sold in each cinema contains two parts: official and retail. If there is no ticket sales record for a certain cinema, it means 0.

I want to get the following output:

And I tried:

WITH CombinedTickets AS (
    SELECT
        o.Cinema,
        o.OfficialSold,
        r.RetailSold,
        (COALESCE(o.OfficialSold,0) + COALESCE(r.RetailSold,0)) AS TotalTickets
    FROM Official o
    LEFT JOIN Retail r ON o.Cinema = r.Cinema
), 
MaxTickets AS (
    SELECT 
        MAX(TotalTickets) AS MaxTotal
    FROM CombinedTickets
)
SELECT
    Cinema,
    COALESCE(OfficialSold,0),
    COALESCE(RetailSold,0),
    TotalTickets
FROM CombinedTickets
CROSS JOIN MaxTickets
WHERE TotalTickets = MaxTotal
ORDER BY Cinema;

But I got this answer, I don’t know why there is no cinema 6. Could anyone provide a correct method?(If you don’t know the database I use, you can use PostgreSQL instead, thanks!)