Re: conditional select sum() based on field question

> In article <297@praeda.UUCP.UUCP> pete@praeda.UUCP.UUCP (Pete Frehner) asked: > > > >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...I could do this in two select statements... > > 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? In article <1992Aug13.003128.8803@informix.com> proberts@informix.com (Paul Roberts) supplied a very clever answer: > > create a temp table called "fudge" and put exactly > two rows into it, like so: > 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 > This is exceedingly clever, but what strikes me is how it generalizes to the application of any kind of "weight" against any set of categorized "samples." In this case the "samples" were money; there were just two "categories," and the "weights" were +/- 1 -- but that is not inherent. This is a general technique that can apply arbitrary weights based on an arbitrary number of categories. For one other commercial example, let the weights be a series of numbers between 0 and 1 representing state sales tax percentages, and let the categories be names of states. Now you can sum the sales tax due on a column of retail prices with one select. Or, multiply by 1+weight to sum the prices including tax. Or let the amounts be shipping weights; let the categories be delivery zones; let the weights be shipping charge per-pound multipliers. And of course the "samples" and "weights" could actually be samples and weights in a statistical sense.