CODEWITHSUNDEEP
sqlpostgresql
Andrés Rinaldi
Andrés Rinaldi

Reputation: 1

MAX(COUNT(*)) for each ID in a SQL query

I'm needing some help with a SQL query using PostgreSQL 9.4.

I need the most rented movies on each local, this is the data I'm asked to select

Tables: 

rental(idMovie, idLocal, idClient)
movies(idMovie, title, description, year)

This is what I have done, but is not what i am asked to do.

SELECT m.tile, m.year, r.idLocal, COUNT(*) AS cont
FROM rental r, movies m
WHERE m.idMovie=r.idMovie
GROUP BY r.idLocal, m.title, m.year
ORDER BY COUNT(*) DESC;

Upvotes: 0

Views: 181

Answers (2)

jpw
jpw

Reputation: 44891

If I understand your question correctly what you want is to show the most rented out movie(s) for every location.

If that is the case then you could use a window function like rank() which will assign a ranking number to all movies based on the number of rentals for each location. The where clause then filters out the highest ranking movies (there can of course be more than one that shares the top spot).

select m.title, m.year, r.idLocal, r.rents
from movies m 
join (
  select 
    idMovie, 
    idlocal,
    count(idmovie) rents,
    rank() over (
      partition by idlocal 
      order by count(idmovie) desc
    ) rn
  from rental 
  group by idMovie, idLocal
) r on m.idMovie = r.idMovie 
where rn = 1
order by idlocal;

Sample SQL Fiddle

Upvotes: 1

colintobing
colintobing

Reputation: 167

Try this...

SELECT r.idLocal, m.title, m.year, count(r.idMovie) as count_movie
FROM rental r, movies m
WHERE m.idMovie=r.idMovie
GROUP BY r.idLocal, m.title, m.year
ORDER BY count_movie DESC;

Or

SELECT r.idLocal, m.title, m.year, count(r.idMovie) as count_movie
FROM rental r, movies m
WHERE m.idMovie=r.idMovie
GROUP BY r.idLocal, m.title, m.year
ORDER BY count(r.title) DESC;

Upvotes: 0

Related Questions