I'm trying to insert data into a table and running into a problem with using the COUNT function. The count_rentals and count_returns columns are supposed to represent the number of times a given movie was rented and returned.
The dataset (sakila) from which I'm trying to insert the data into my table doesn't have the number of rentals and returns tallied up, instead, it has a return_date and rental_date column. I figured using COUNT on those two columns would give me the number of times the movie was rented and returned (since when it's rented a date will appear in the rental date and when it's returned, a date will appear in the returned date), so I wrote this:
INSERT INTO sakila_snowflake.fact_rental (
rental_id,
rental_last_update,
customer_key,
staff_key,
film_key,
store_key,
rental_date_key,
return_date_key,
count_returns,
count_rentals,
rental_duration,
dollar_amount)
(SELECT
s_rental.rental_id,
s_rental.last_update,
s_customer.customer_id,
s_staff.staff_id,
s_film.film_id,
s_store.store_id,
s_rental.rental_date,
s_rental.return_date,
Count(s_rental.rental_date),
Count(s_rental.return_date),
s_rental.return_date - s_rental.rental_date,
(s_rental.return_date - s_rental.rental_date)*s_film.rental_rate
FROM
sakila.rental as s_rental,
sakila.customer as s_customer,
sakila.staff as s_staff,
sakila.film as s_film,
sakila.store as s_store
WHERE
s_rental.staff_id = s_staff.staff_id AND s_staff.store_id=s_store.store_id AND s_store.store_id = s_customer.store_id);
However, as soon as I tried running it, I got this error:
Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'sakila.s_rental.rental_id'; this is incompatible with sql_mode=only_full_group_by.
I'm not quite sure how to obtain the number of times a movie was rented and returned for the count_rental and count_return columns without using COUNT function. If anyone has any suggestions, I'll be extremely grateful :)
