Re: conditional select sum() based on field question

In article <297@praeda.UUCP.UUCP> pete@praeda.UUCP.UUCP (Pete Frehner) writes: > >I have a question regarding selecting a sum from a table of amounts and >depending upon the type of record, either adding or subtracting the amount >from the sum. We are using Informix 4.0 ISQL and R4GL. > >I could do this in two select statements and the difference would be the >net amount, but this requires that the data be read twice (once for each >select statment). > > select sum(amount) into total_credits > from orders where order_type = "CREDIT" > > select sum(amount) into total_normal_orders > from orders where order_type = "REG" > > let net_amount = total_normal_orders - total_credits > > >Is there a way that this could be done in one select statment?? Two select >statments is no big deal for a small database, but this database contains >hundreds of thousands of orders. If the data could be read only once it would >pick up performance quite a bit. > The way I would choose to do it is: store the amounts as negative numbers on "CREDIT" orders. i.e. treat the credit memo as an order which happens to be for a negative sum of money. Then you can just sum() the lot and not worry about distinguishing the two types. If this is not an option (e.g. because the system is already in place, and the dollar amounts on credit memos are positive, even though Paul Roberts thinks they should be negative), then there is this approach: create a temp table called "fudge" and put exactly two rows into it, like so: temp table fudge: ----------------- char(6) smallint order_type f_factor ---------- -------- REG 1 CREDIT -1 and then do this select: select sum(orders.amount * fudge.f_factor) from orders, fudge where fudge.order_type = orders.order_type The idea is that the f_factor will change the sign on the dollar amount to a negative value on the credit memos, just as the gods intended. Whether this will help performance is another question though, since you do have to do the join (albeit to a very small table) and the arithmetic. Might be worth a try though. Paul p.s. Tell your friends: qty_shipped and dollar_amount should *both* be negative for credit memos! "price" stays +ve, though. Have I suffered because I worked on a system where this isn't the case? Yes, I have.