CODEWITHSUNDEEP
mysqlsql
Nate
Nate

Reputation: 28384

How to return rows based on column values and grouped by date?

I have a table with this structure:

movies:

id | date_entered_theater | date_left_theater

I want to write a query that returns the number of movies in the theater on each day. A movie is in the theater when the date_entered_theater column is not null and the date_left_theater column is null.

The date columns use the datetime format and the query should return data such as this:

date         moviesInTheater
2014-09-22 | 5
2014-09-23 | 6
2014-09-24 | 8
2014-09-25 | 7

I'm unsure of how to write this query. I tried the following:

SELECT DATE(date_entered_theater), COUNT(*) as moviesInTheater
FROM movies
GROUP BY DATE(date_entered_theater)
HAVING date_entered_theater IS NOT NULL AND date_left_theater IS NULL
ORDER BY date_entered_theater ASC

It results in the error [Err] 1054 - Unknown column 'date_left_theater' in 'having clause'

I also don't think the above query is correct because it groups by date_entered_theater and doesn't take date_left_theater into account, which means a movie could leave the theater on a day no movies enter the theater and this change wouldn't be reflected in the result set.

Can what I'm asking be done in MySQL?

Upvotes: 1

Views: 60

Answers (2)

psadac
psadac

Reputation: 2342

First you need a table with all days for the time span you care (one year for example) in one column. You can't use other tables because there is no garantee that you have all days present in the column movies.date_entered_theater.

You can fill it with a loop using your favorite programming language.

Let suppose this table exists, is named movies_daily and has one column day the primary key.

Then a movie is in theater if the current day is between the date the movie entered the theater and the date it left the theater.

If the date the movie left the theater is null the movie is still in the theater :

SELECT
  movies_daily.day
  , COUNT(*) AS moviesInTheater
FROM
  movies_daily
  LEFT JOIN movies
    ON movies_daily.day >= movies.date_entered_theater
    AND (movies_daily.day < movies.date_left_theater 
         OR movies.date_left_theater IS NULL)
GROUP BY
  movies_daily.day
ORDER BY
  movies_daily.day

Upvotes: 1

user2989408
user2989408

Reputation: 3137

Your conditional check should be in WHERE and not in HAVING.
Only conditions based on aggregate functions should be in HAVING clause.

SELECT DATE(date_entered_theater), COUNT(*) as moviesInTheater
FROM movies
WHERE date_entered_theater IS NOT NULL AND date_left_theater IS NULL
GROUP BY DATE(date_entered_theater)
ORDER BY date_entered_theater ASC

EDIT: If you need to find the number of movies playing on any particular date, you may need something like the one below. I will try to post a fiddle when I have some time.

SET @num = -1;
SELECT
    date_sequence,
    COUNT(m.id) as moviesInTheater
FROM movies as m
    JOIN (SELECT DATE_ADD( '2014-09-01', 
                  interval @num := @num+1 day) AS date_sequence
          HAVING DATE_ADD('2014-09-01', interval @num day) <= '2014-09-25') as dt
         ON m.date_entered_theater >=dt. date_sequence
GROUP BY dt.date_sequence

Upvotes: 2

Related Questions