Link to home
Start Free TrialLog in
Avatar of Rob_Jeffrey
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)
ASKER CERTIFIED SOLUTION
Avatar of tdterry
tdterry
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rob_Jeffrey
Rob_Jeffrey

ASKER

Bloody brilliant.
Having worked fine on the alias.

Thank you.