14,870,621 members
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

## Solution 1

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
;```
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[^]

Top Experts
Last 24hrsThis month
 OriginalGriff 165 Richard MacCutchan 143 mike@codeproject 90 Patrice T 70 NISHANK BABU 35
 OriginalGriff 1,979 Richard MacCutchan 1,153 Patrice T 485 Dave Kreskowiak 435 CPallini 305

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