How to select the result of an operation inside a COUNT ?

Question

0.00/5 (No votes)

See more:

I have a table Movies and another table reservationOfMovie.
ReservationsOfMovie has a column dateOfReservation (which is a DATE)

I want to show both the amount of movies which are currently reserved and the amount of those which are available.
What I'm trying to do is to select the count of records where the

dateOfReservation

is earlier or after the current date, but I can't do such operation.

What I have tried:

DECLARE @Today DATE = GETDATE();

SELECT COUNT (r.dateOfReservation< @Today ) AS 'Available',
       COUNT (r.dateOfReservation> @Today ) AS 'notAvailable'
FROM Movies m
INNER JOIN ReservationsOfMovie r
ON r.IDMovie = m.IDMovie

Posted 5-Nov-20 0:42am

xhon

Updated 5-Nov-20 1:05am

v2

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-05T01:05:00

Solution 1

SQL

DECLARE @Today date = GETDATE();

-- Currently reserved:
SELECT Count(DISTINCT IDMovie)
FROM ReservationsOfMovie
WHERE @Today Between StartDate And EndDate;

-- Currently available:
SELECT Count(*)
FROM Movies As m
WHERE Not Exists
(
    SELECT 1
    FROM ReservationsOfMovie As r
    WHERE r.IDMovie = m.IDMovie
    And @Today Between r.StartDate And r.EndDate
);

Posted 5-Nov-20 1:05am

Richard Deeming

Comments

xhon 5-Nov-20 7:13am

Hi, thanks for your answer. How can I put both in one select?

Richard Deeming 5-Nov-20 7:14am

Use sub-queries:

SELECT
     (SELECT ...) As CurrentlyReserved,
     (SELECT ...) As CurrentlyAvailable
;

xhon 5-Nov-20 8:19am

what if each movie has N copis and I want to show the director, the total number of copies, the number of available ones and the number of copies which are currently reserved?
I'm trying to do this:

SELECT
Director
Number_of_copies, (
SELECT
Number_of_copies
FROM
Movies m
INNER JOIN
ReservationsOfMovies r
ON
r.IDMovie = m.IDMovie
WHERE
@Today BETWEEN StartDate AND EndDate

)
AS Currently_Reserved,

(
SELECT
Quantity
FROM
Movies m
INNER JOIN
ReservationsOfMovies r
ON
r.IDMovies = m.IDMovies
WHERE
@Current_Date > EndDate

)
AS Currently_Available

FROM Movies
GROUP BY Drector, Quantity

Richard Deeming 5-Nov-20 10:10am

Something like this:

SELECT
    Director,
    Sum(Number_of_copies) As TotalCopies,
    Sum(SELECT Count(1) FROM ReservationsOfMovies As r WHERE r.IDMovie = m.IDMovie And @Today Between r.StartDate And r.EndDate)) As TotalReserved,
    Sum(Number_of_copies) - Sum(SELECT Count(1) FROM ReservationsOfMovies As r WHERE r.IDMovie = m.IDMovie And @Today Between r.StartDate And r.EndDate)) As TotalAvailable
FROM
    Movies As m
GROUP BY
    Director
;

xhon 5-Nov-20 11:38am

Than you very much. In your opinion could I obtain the same result by using an outer join?

Richard Deeming 5-Nov-20 11:42am

Probably, but you'd need to join to a sub-query:

SELECT
    m.Director,
    Sum(m.Number_of_copies) As TotalCopies,
    Sum(r.TotalReserved) As TotalReserved,
    Sum(m.Number_of_copies) - Sum(r.TotalReserved) As TotalAvailable
FROM
    Movies As m
    OUTER JOIN
    (
        SELECT IDMovie, Count(1) As TotalReserved
        FROM ReservationsOfMovies
        WHERE @Today Between StartDate And EndDate
        GROUP BY IDMovie
    ) As r
    ON r.IDMovie = m.IDMovie
GROUP BY
    m.Director
;