How can I group-by the last 3 months for a particular title?-CodePudding

Pretty much, I have a database of movies in a table called MovieDatabase.

Movie_Title    | Sales | startdateshowing
---------------------------------------------
Pulp Fiction   | 2.99. | 2018-06-05
Pulp Fiction   | 2.99  | 2018-06-02
Pulp Fiction   | 2.99. | 2017-10-21
Pulp Fiction   | 2.99. | 2016-08-21
Pulp Fiction.  | 2.99. | 2015-06-13
Reservoir Dogs | 3.99  | 2021-11-04
Reservoir Dogs | 3.99. | 2021-11-01
Reservoir Dogs | 3.99. | 2021-10-23
Reservoir Dogs | 3.99  | 2021-10-05
Reservoir Dogs | 3.99  | 2021-09-23
Interstellar.  | 0.99  | 2021-10-07
Interstellar   | 0.99  | 2021-08-21
Licorice Pizza | 1.99  | 2022-02-22
12 Angry Men.  | 4.99  | 2022-01-19
12 Angry Men.  | 4.99  | 2021-12-24
12 Angry Men.  | 4.99  | 2021-11-27

What I'm trying to query here, is the SUM of all Sales for a Movie Title, in the last three months that the title was shown. So a little unconventional compared to just checking a title's performance in the last 3 months relative to todays date.

I have a query I'm sort of working on like:

SELECT Movie_Title, SUM(Sales) Total_Sales, LatestStartDate, EarliestStartDate
FROM MovieDatabase
GROUP BY Movie_Title
WHERE ?

So like, my goal result set returned for Pulp Fiction should be like

Movie_Title   | Total_Sales | LatestStartDate   | EarliestStartDate
----------------------------------------------------------------
 
Pulp Fiction  | 11.96.      | 2018-06-05        | 2016-08-21
Reservoir Dogs| 19.95       | 2021-11-04        | 2021-09-23
Interstellar  | 1.98        | 2021-10-07        | 2021-08-21
Licorice Pizza| 1.99.       | 2022-02-22.       | 2022-02-22
12 Angry Men  | 14.97       | 2022-01-19        | 2021-11-27

In which lines from the result set like:

Pulp Fiction   | 2.99. | 2016-08-21
Pulp Fiction.  | 2.99. | 2015-06-13

would be omitted. Hope this all makes sense. I can clarify any confusion.

Thanks

CodePudding user response：

SELECT  Movie_Title,
        SUM(md.Sales) AS SalesInLast3Months,
        MAX(md.startdateshowing) AS LatestStartDate,
        MIN(md.startdateshowing) AS EarliestStartDate
    FROM ( SELECT  Movie_Title,
                   MAX(startdateshowing) - INTERVAL 3 MONTH AS start
              FROM  MovieDatabase
              GROUP BY  Movie_Title 
         ) AS x
    JOIN  MovieDatabase AS md USING (Movie_Title)
    WHERE  startdateshowing >= start
    GROUP BY  Movie_Title

CodePudding user response：

You need a HAVING clause to filter out the most recent three months.

SELECT
  startdateshowing,
  Movie_Title,
  SUM(Sales) AS Total_Sales,
  MAX(startdateshowing) AS LatestStartDate,
  MIN(startdateshowing) AS EarliestStartDate
FROM
  MovieDatabase
GROUP
  BY Movie_Title
HAVING
  startdateshowing >= SUBDATE( MAX( startdateshowing ), INTERVAL 3 MONTH )