Menu
I need to find in sakila database the longest rental period of a movie. I have tried this:
SELECT DISTINCT customer.first_name FROM rental, customer WHERE rental.customer_id = customer.customer_id GROUP BY rental.rental_id HAVING ( rental.return_date - rental.rental_date ) =( SELECT MAX(countRental) FROM ( SELECT ( rental.return_date - rental.rental_date ) AS countRental FROM rental, customer GROUP BY rental.rental_id ) AS t1 ) but I am getting the error:
# 1054 - Unknown column 'rental.return_date' in 'having clause' Does anybody know why? I have used a column that's supposed to be the aggregated data. What am i missing?
389
The MySQL unknown column in field list error happens when you put a column name in your SQL script that can't be found by MySQL.
You can create a WHERE clause and HAVING clause involving the same column. To do so, you must add the column twice to the Criteria pane, then specify one instance as part of the HAVING clause and the other instance as part of the WHERE clause.
Having can be used without groupby clause,in aggregate function,in that case it behaves like where clause. groupby can be used without having clause with the select statement. 3. The having clause can contain aggregate functions.
We cannot use the HAVING clause without SELECT statement whereas the WHERE clause can be used with SELECT, UPDATE, DELETE, etc. WE can use aggregate functions like sum, min, max, avg, etc with the HAVING clause but they can never be used with WHERE clause. HAVING clause is generally used with the GROUP BY.
As written in the documentation
The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.
You have to specify return_date and rental_date in the select clause.
There are two options:
SELECT DISTINCT customer.first_name, rental.return_date, rental.rental_date FROM rental, customer WHERE rental.customer_id = customer.customer_id GROUP BY rental.rental_id HAVING ( rental.return_date - rental.rental_date ) =( ... or
SELECT DISTINCT customer.first_name, (rental.return_date - rental.rental_date) as rental_duration FROM rental, customer WHERE rental.customer_id = customer.customer_id GROUP BY rental.rental_id HAVING rental_duration =( ... Both should work just fine.
169
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
