Is there a way where I can use MAX(Count(some_table

I am unsure how to find the maximum of a count function, for example

Select Count(Seat_Reservation.Customer_phone) as MaxTicketsBooked
, Seat_Reservation.Customer_phone
From Seat_Reservation
Group By Seat_Reservation.Customer_phone
Having Count(Seat_Reservation.Customer_phone) = (
    Select MAX(MaxTicketsBooked)
    From (
        Select Count(Seat_Reservation.Customer_phone) As MaxTicketsBooked
        From Seat_Reservation
        Group By Seat_Reservation.Customer_phone
    ) As Maxed
);

This code works for me, however, I need to display CustomerNames which produces the error: Column 'Seat_Reservation.Customer_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is there a way in which I could find the maximum of the count produced by

Select Count(Seat_Reservation.Customer_phone) as MaxTicketsBooked,

Thank you in advance!

CodePudding user response：

You didn't post the code that failed, so I'll guess:

Select Count(Seat_Reservation.Customer_phone) as MaxTicketsBooked
, Seat_Reservation.Customer_phone
, Seat_Reservation.Customer_name
From Seat_Reservation
Group By Seat_Reservation.Customer_phone
Having Count(Seat_Reservation.Customer_phone) = (
    Select MAX(MaxTicketsBooked)
    From (
        Select Count(Seat_Reservation.Customer_phone) As MaxTicketsBooked
        From Seat_Reservation
        Group By Seat_Reservation.Customer_phone
    ) As Maxed
);

And the error message makes perfect sense.

Column 'Seat_Reservation.Customer_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The solution is right in the error message.

Select Count(Seat_Reservation.Customer_phone) as MaxTicketsBooked
, Seat_Reservation.Customer_phone
, Seat_Reservation.Customer_name
From Seat_Reservation
Group By Seat_Reservation.Customer_phone
, Seat_Reservation.Customer_name
Having Count(Seat_Reservation.Customer_phone) = (
    Select MAX(MaxTicketsBooked)
    From (
        Select Count(Seat_Reservation.Customer_phone) As MaxTicketsBooked
        From Seat_Reservation
        Group By Seat_Reservation.Customer_phone
    ) As Maxed
);