Difference between two columns in mysql
14,293
You just need to add the logic into the where
clause:
SELECT `p_Id`,`user_id`,`doc_id`,`credit` ,`app_date`,`expires_on`,
(credit -debited_amount) AS credit
FROM `wp_loyalty_credits`
WHERE `expires_on`>now() and (credit > debited_amount or debited_amount is null);
Your query redefines credit
in the select
. However, that is irrelevant, because you can't refer to a column alias in the where
clause. So, the column credit
is what it used. It is clearer if you add table aliases:
SELECT lc.p_Id, lc.user_id, lc.doc_id, lc.credit, lc.app_date, lc.expires_on,
(lc.credit - lc.debited_amount) AS credit
FROM `wp_loyalty_credits` lc
WHERE lc.expires_on > now() and
(lc.credit > lc.debited_amount or lc.debited_amount is null);
Author by
user3431651
Updated on June 04, 2022Comments
-
user3431651 almost 2 years
I have two columns (
credit
anddebited_amount
) and I want to calculate the difference between them.Only need to retrieve records greater than zero or if the
debited_amount
field is Null.Never mind if the value is zero.
Here is the SQL query which I have tried. Please help
SELECT `p_Id`, `user_id`, `doc_id`, `credit`, `app_date`, `expires_on`,(credit - debited_amount) AS credit FROM `wp_loyalty_credits` WHERE `expires_on` > now();