Re: conditional select sum() based on field question

In article <297@praeda.UUCP.UUCP> pete@praeda.UUCP.UUCP (Pete Frehner) writes: >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. This does the *important* work in one select: select order_type, sum(amount) sum_amount from orders where order_type in ("CREDIT", "REG") group by order_type into temp tempsum The queries to populate the variables are really quick, since they are dealing with a two-row summarized temp table at this point: select sum_amount into total_credits from tempsum where order_type = "CREDIT" select sum_amount into total_normal_orders from tempsum where order_type="REG" let net_amount = total_normal_orders - total_credits >Pete Frehner Voice (519) 673-3350 >Praeda Management Systems Fax (519) 667-1968 >371 Dufferin Ave. uucp: .....uunet!praeda!pfrehner >London, Ontario CANADA N6B 1Z5 -- Alan Denney aland@informix.com {pyramid|uunet}!infmx!aland Smith and Wesson: the Ultimate Point-and-Click User Interface.