Member Avatar for yohgurt

Could you help me please? I have a table List:

        List(
        cinema char (15)
        movie char (20))

I need to get movies which are shown in most cinemas and the number of times they are shown. If there are more then one movie shown the same number of times, there must be all of them.

All I wrote yet is:

SELECT MAX(CNT.cout) as  maximum
     FROM (SELECT movie, COUNT(ALL cinema) as cout FROM List GROUP BY movie) as CNT

but it just shows the number of times the most shown move was showned...

Edited by yohgurt
  • 1 Contributor
  • 1 Reply
  • 123 Views
  • 48 Minutes Discussion Span
  • Latest Post Latest Post by yohgurt
Member Avatar for yohgurt

I also tried

FROM (SELECT movie, COUNT(cinema) as cout FROM PC GROUP BY movie) as CNT INNER JOIN List
ON CNT.moviel = List.movie
GROUP BY CNT.movie, CNT.cout, PC.cinema
HAVING CNT.cout=(SELECT max(CONT.cinemas) as Maximum
                  FROM(SELECT movie, COUNT(cinema) as cinemas 
                        FROM List 
                        GROUP BY movie) as CONT)

but it doesn't seem working

UPD: OMG, it worked! Sorry to bother you, it's just I spent a few hours to get it.

Edited by yohgurt
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.