NetNews Usenet Archive 1992 #18/NN_1992_18.iso/spool/comp/database/informix/1716

Internet Message Format | 1992-08-13 | 1.5 KB

Path: sparky!uunet!cs.utexas.edu!uwm.edu!ogicse!das-news.harvard.edu!das!bochner
From: [e-mail] (Harry Bochner)
Newsgroups: comp.databases.informix
Subject: Re: conditional select sum() based on field question
Keywords: select sum
Message-ID: <[e-mail]>
Date: 13 Aug 92 18:06:45 GMT
Article-I.D.: das.1992Aug13.180645.6534
References: <[e-mail]>
Sender: [e-mail] (Network News)
Organization: Aiken Computation Lab, Harvard University
Lines: 28

In article <[e-mail]>, [e-mail] (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
[e-mail]