Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 788
  • Last Modified:

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)
0
Rob_Jeffrey
Asked:
Rob_Jeffrey
1 Solution
 
tdterryCommented:
The error is because WHERE can't operate on an alias (i.e. "neg").  From the Manual (http://dev.mysql.com/doc/refman/5.0/en/select.html):

    'It is not allowable to use a column alias in a WHERE clause, because the column value might not yet be determined when the WHERE clause is executed. See Section A.5.4, “Problems with Column Aliases”.'

This is exactly your case.  The value is only known after grouping and computing the aggregate sum().  You have to use a HAVING clause to filter on an aggregate function like sum(), min(), max(), etc.  If the sum is negative, that means you have an open order with no (or too few) payments.

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
HAVING neg < 0

If you still get the error, then you need "HAVING sum(if(class='t_invoice' , 0-value, value)) < 0".  I don't remember if MySQL lets you use the alias in the HAVING clause.
0
 
Rob_JeffreyAuthor Commented:
Bloody brilliant.
Having worked fine on the alias.

Thank you.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now