Posted by Katie, Feb. 9, 2022, 8:39 a.m.
Group By clause for window function
I have the question being asked to create a rolling 7 day rental rate average for August 2020, i am using this query:
SELECT
DATE(rental_ts) AS rental_date
,AVG(rental_rate) OVER (PARTITION BY rental_rate
ORDER BY (DATE(rental_ts))
ROWS BETWEEN
6 PRECEDING AND
CURRENT ROW
) as rolling_avg
FROM rental
LEFT JOIN inventory
ON rental.inventory_id = inventory.inventory_id
LEFT JOIN film
ON film.film_id = inventory.film_id
WHERE rental_ts BETWEEN '2020-08-01' AND '2020-08-31'
GROUP BY DATE(rental_ts)
But am getting this error: "column "film.rental_rate" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: ,AVG(rental_rate) OVER (PARTITION BY rental_rate ^" which makes no sense because rental_rate is already being used in an aggregate function, can anyone help with why this is happening??
​
SELECT
DATE(rental_ts) AS rental_date
,AVG(rental_rate) OVER (PARTITION BY rental_rate
ORDER BY (DATE(rental_ts))
ROWS BETWEEN
6 PRECEDING AND
CURRENT ROW
) as rolling_avg
FROM rental
LEFT JOIN inventory
ON rental.inventory_id = inventory.inventory_id
LEFT JOIN film
ON film.film_id = inventory.film_id
WHERE rental_ts BETWEEN '2020-08-01' AND '2020-08-31'
GROUP BY DATE(rental_ts)
But am getting this error: "column "film.rental_rate" must appear in the GROUP BY clause or be used in an aggregate function LINE 3: ,AVG(rental_rate) OVER (PARTITION BY rental_rate ^" which makes no sense because rental_rate is already being used in an aggregate function, can anyone help with why this is happening??
​
