How to limit results of a sum(ed) if statement

Posted on 2006-04-10
Last Modified: 2010-05-18
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)
Question by:Rob_Jeffrey
    LVL 5

    Accepted Solution

    The error is because WHERE can't operate on an alias (i.e. "neg").  From the Manual (

        '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.

        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.
    LVL 9

    Author Comment

    Bloody brilliant.
    Having worked fine on the alias.

    Thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
    Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now