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). ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ This implies that the cardinality/selectivity of "order_type" is such that "order_type" is not indexed. > > > 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. Try this: create table foo ( order_type char(6), bar integer ); insert into foo values("CREDIT", -1); insert into foo values("REG", 1); select sum(O.amount * F.bar) from orders O, foo F where O.order_type = F.order_type and O.amount is not null; Note: foo could be an existing permanent table or it could be created/dropped on the fly.