How to get a calculated output as mysql column?

May 31, 2022

I need to make a calculation by using a mysql query.

Here is my query.

SELECT  book_name, 
CASE WHEN  CURDATE()<book_return THEN 0 ELSE DATEDIFF(CURDATE(),book_return) END AS DateDifference,
CASE WHEN  DateDifference>0 THEN DateDifference*10 ELSE NULL) END AS TotalFines  FROM tblIssuedBooks order by lastupdated DESC

I need to mutiply DateDifference column by 10 if the DateDIfference value is greater than zero.but when I execute this I am getting Unknown column 'DateDifference' in 'field list' as an error.

Can someone show me how to improve this?

>Solution :

You can’t reuse an alias in a select which was defined in the same select. One workaround here uses a subquery:

SELECT book_name, DateDifference,
       CASE WHEN DateDifference > 0 THEN DateDifference*10 END AS TotalFines
FROM
(
    SELECT *, CASE WHEN CURDATE() < book_return
                   THEN 0
                   ELSE DATEDIFF(CURDATE(), book_return) END AS DateDifference
    FROM tblIssuedBooks
) t
ORDER BY lastupdated DESC;