Rob_Jeffrey
asked on
How to limit results of a sum(ed) if statement
I am slowly trying to dump more of the processing onto the MySQL Server.
I have an online order system - each order when invoiced gets added to a seperate table, accounts_trans (Account transactions).
Transaction classes are t_payment, t_credit, or t_invoice. I am trying to add up the outstanding value of an order. The value is always a positive number - what it represents (a positive or negative) will depend solely on the perspective of the current query. Adding up the order - t_invoice is a negative number.
This sql statement works great to add the values and group by the order id:
SELECT orders_id, users_id, date_added, class, value, comments,
sum(if(class='t_invoice' , 0-value, value)) as neg
FROM accounts_trans
group by orders_id
I would like to have only the orders which are still outstanding to be returned. I can't figure out how to structure the query to return only the rows where `neg` gets evaluated to less than 0.
Adding a "WHERE neg < 0" right before the group by clause I get the error "#1054 - Unknown column 'neg' in 'where clause'"
Normally I would simply run the query and loop through all rows in PHP but I am looking to expand my knowledge and skill with better ways to use the power of the SQL language.
Thanks everyone!
(using MySQL 4.1.18 and PHP Version 5.0.5)
I have an online order system - each order when invoiced gets added to a seperate table, accounts_trans (Account transactions).
Transaction classes are t_payment, t_credit, or t_invoice. I am trying to add up the outstanding value of an order. The value is always a positive number - what it represents (a positive or negative) will depend solely on the perspective of the current query. Adding up the order - t_invoice is a negative number.
This sql statement works great to add the values and group by the order id:
SELECT orders_id, users_id, date_added, class, value, comments,
sum(if(class='t_invoice' , 0-value, value)) as neg
FROM accounts_trans
group by orders_id
I would like to have only the orders which are still outstanding to be returned. I can't figure out how to structure the query to return only the rows where `neg` gets evaluated to less than 0.
Adding a "WHERE neg < 0" right before the group by clause I get the error "#1054 - Unknown column 'neg' in 'where clause'"
Normally I would simply run the query and loop through all rows in PHP but I am looking to expand my knowledge and skill with better ways to use the power of the SQL language.
Thanks everyone!
(using MySQL 4.1.18 and PHP Version 5.0.5)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Having worked fine on the alias.
Thank you.