SQL evaluar 2 condiciones en una subconsulta

[maxi9308]

Buenas, le hago una consulta..
Tengo una tabla de prestamos compuesta de la siguiente manera:
Prestamos(codPrestamo,CodPelicula,CodCliente,Fecha Prest)

Lo que necesito obtener es la película mas vendida(prestada en este caso) del mes en mi video Club. Es decir se tiene que listar el mes, CodPelicula, cantAlquileres.

Yo emplee este codigo, pero no me las separa por mes, es decir, me da la que mayor alquileres tuvo en la historia y no por meses.

Código SQL:

SELECT MONTH(FechaPrest)AS mes,CodPel,COUNT(CodPel) AS alquileres
FROM Prestamo
GROUP BY MONTH(FechaPrest),CodPel
HAVING COUNT (CodPel)=(
SELECT MAX(t.alq) AS maximo
FROM(
    SELECT MONTH(FechaPrest) AS mes,CodPel,COUNT(CodPel) AS alq
    FROM Prestamo
    GROUP BY MONTH(FechaPrest),CodPel
    )t
)
 

Lo que no estoy supiendo sacar es como puedo hacer que esa subconsulta me devuelva el mes y evaluarlo junto con el max de alquileres que tuvo.

Gracias

[Libras]

puedes poner un ejemplo de tus datos???

[quinqui]

Holas, maxi9308.

Tal vez si usas alias en las tablas, ya que estás consultando la misma, con 2 objetivos distintos. Te faltaría cruzar el mes, como dices, prueba con lo que te escribo acá, a veri si resulta:

Código sql:

SELECT MONTH(FechaPrest)AS mes,CodPel,COUNT(CodPel) AS alquileres
FROM Prestamo P1
GROUP BY MONTH(FechaPrest),CodPel
HAVING COUNT (CodPel)=(
    SELECT MAX(t.alq) AS maximo
    FROM
    (
        SELECT MONTH(FechaPrest) AS mes,CodPel,COUNT(CodPel) AS alq
        FROM Prestamo
        GROUP BY MONTH(FechaPrest),CodPel
    ) t
    WHERE t.mes = MONTH(P1.FechaPrest)
)
 

Saludos!