CODEWITHSUNDEEP
mysqldatediff
Pancakeeeeeeeeeeeeeeeeeee
Pancakeeeeeeeeeeeeeeeeeee

Reputation: 1

MySQL getting column using DATEDIFF inside IF

I'm trying to get column 'Returned On Time' using DATEDIFF and IF functions

SELECT
IF(DATEDIFF(day, rental.rental_date, rental.return_date) <= film.rental_duration, 
   (COUNT(rental.rental_id) AS 'Returned On Time'))
FROM rental, film

But all I get is error, and I don't really understand how exactly DATEDIFF works and I don't even know if I can use COUNT inside the IF function

Thank you all in advance!!!

Upvotes: 0

Views: 50

Answers (1)

Barmar
Barmar

Reputation: 781848

You can't use an aggregate function in the consequence of an IF. The IF expression is being applied to a single row, it doesn't make sense to aggregate the result.

You're also missing the joining condition between the two tables.

And the arguments you're using for DATEDIFF() are incorrect, they're more appropriate for TIMESTAMPDIFF. DATEDIFF() only takes two arguments, and the later one should be first.

I think what you want is this:

SELECT SUM(TIMESTAMPDIFF(day, rental.rental_date, rental.return_date) <= film.rental_duration) AS returned_on_time
FROM rental 
JOIN film ON rental.film_id = film.id

Upvotes: 1

Related Questions