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
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.
(using MySQL 4.1.18 and PHP Version 5.0.5)