Click here to Skip to main content
14,870,621 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table of movies and another table of reservations which keep tracks of the reservations for the movies (it has a StartDate and an EndDate).

I want to show the year when the maximum number of videos have been reserved.

What I have tried:

I did the following:

SELECT StartDate
FROM ReservatonsOfTheMovies res
INNER JOIN Movies mov ON mov.IDMovie = res.IDMovie
WHERE MAX (COUNT (StartDate))
Posted
Updated 4-Nov-20 0:18am

1 solution

For the maximum number of reservations:
SQL
SELECT TOP 1
    Year(StartDate) As [Year],
    Count(1) As Reservations
FROM
    ReservatonsOfTheMovies res
GROUP BY
    Year(StartDate)
ORDER BY
    Reservations DESC
;
For the maximum number of different movies reserved in a year:
SQL
SELECT TOP 1
    Year(StartDate) As [Year],
    Count(DISTINCT IDMovie) As Reservations
FROM
    ReservatonsOfTheMovies res
GROUP BY
    Year(StartDate)
ORDER BY
    Reservations DESC
;
   
Comments
xhon 4-Nov-20 6:37am
   
thanks! What if I wanted to select all the Directors (Name and Surname) who made more than 10 movies?
I would like to order them by the number of books they wrote, from the maximum to the minimum.
I did the following:
SELECT CONCAT (DirctorName, DirectorSurname) AS Director
FROM Movies
WHERE COUNT(*) > 10
ORDER BY COUNT(*) DESC
Richard Deeming 4-Nov-20 7:18am
   
SELECT CONCAT(DirctorName, DirectorSurname) AS Director, Count(*) As MovieCount
FROM Movies 
GROUP BY CONCAT(DirctorName, DirectorSurname)
HAVING Count(*) > 10
ORDER BY MovieCount DESC

GROUP BY (Transact-SQL) - SQL Server | Microsoft Docs[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900