Re: conditional select sum() based on field question

In article <297@praeda.UUCP.UUCP>, pete@praeda.UUCP.UUCP (Pete Frehner) writes: |> 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?? Paul Roberts has already posted a good solution for this particular case, where we want the difference of the two totals. I'd just like to contribute an idea for a more general solution, usable if you really need the two separate figures: select order_type, sum(amount) from orders group by order_type into temp totals select amount into total_credits from totals where order_type = "CREDIT" select amount into total_normal_orders from totals where order_type = "REG" This way the large order table only has to be scanned once. Selecting the individual totals should be fast, as long as the number of order_types is small. -- Harry Bochner bochner@das.harvard.edu