SQL Query :: parameters inside a Count?

Question

Let's say I have a table of movie renters with columns:

• UserID
• MovieID
• Rent_Start_date
• Rent_Due_Date

I am trying to achieve an output table that looks like:

[UserID, Count of Movies Due in 1 Day, Count of Movies Due in 1 Week]

Is this possible to do in one single query? I currently have a php script that runs 1 query on movies due in 1 day and another query that runs movies due in 1 week. These two queries are then looped for every user ID, filling in the table essentially slot by slot. This is kind of slow.

By attempting to create this output with only 1 query, I tried something like:

  SELECT UserID, count(movieID)
    FROM MovieTable
GROUP BY movieID

But this doens't create columns of counts of expiration dates.

Is it possible to create a count column that has an arguement such as count( //all those satisfy where Rent_Due_Date - CURDATE() < ONE_WEEK)?

Answer 1

You need to group your results by user, not by movie:

SELECT   UserID,
         SUM(Rent_Due_Date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 1 DAY),
         COUNT(*)
FROM     MovieTable
WHERE    Rent_Due_Date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 1 WEEK
GROUP BY UserID