CODEWITHSUNDEEP
postgresqlfunctioncountmaxaggregate
Anna Boyko
Anna Boyko

Reputation: 25

MAX(COUNT(...)) - ERROR: calls to aggregate functions cannot be nested

I have two tables - ticket(..,event_name,..) and event(..,name,..).

Task:

Write a query that displays the name of the event and the number of tickets for the event for which the largest number of tickets was sold.

The code below get an error: ERROR: calls to aggregate functions cannot be nested

SELECT name, COUNT(event_name) as ticket_count
FROM event
INNER JOIN ticket
ON event.name = ticket.event_name
GROUP BY name
HAVING COUNT(event_name) = MAX(COUNT(event_name));

I know I can't use MAX(COUNT()), but what I should write instead of it for having the similar logic?

I only have the hint from my lecturer :)

COUNT(...)= (SELECT MAX(...) FROM (...))

Upvotes: 1

Views: 1232

Answers (2)

Julius Tuskenis
Julius Tuskenis

Reputation: 1620

A simplest solution I can think of is making use of ordering and LIMIT 1.

SELECT
  name, COUNT(event_name) as ticket_count
FROM event
  LEFT JOIN ticket ON event.name = ticket.event_name
GROUP BY
  name
ORDER BY
  ticket_count DESC
LIMIT 1;

(using LEFT JOIN as you may have events without tickets I guess)

Upvotes: 1

user330315
user330315

Reputation:

You will have to rank the events based on the count, e.g. using the window function dense_rank().

select event_name, ticket_count
from (
  select event_name, 
         count(*) as ticket_count,
         dense_rank() over (order by count(*) desc) as rnk
  from ticket
  group by event_name
) t
where rnk = 1;

The window function dense_rank() is applied after the group by and will calculate the rank based on the number of tickets for that event. Because of the order by ... DESC the even with the highest number of tickets will get a rank of 1.

If there are two events with the same highest number of tickets, both will be listed. If you don't want that, use row_number() instead of dense_rank()

Note that I also removed the event table from the query as it is not needed for this.

Upvotes: 1

Related Questions