How to select a value where the count of the total values of th tabe is greater than N?

Question

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 4-Nov-20 0:10am

xhon

Updated 4-Nov-20 0:18am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Accepted Answer · 2020-11-04T00:19:00

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
;

Posted 4-Nov-20 0:19am

Richard Deeming

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