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

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

Path: sparky!uunet!praeda.UUCP!pete
From: [e-mail] (Pete Frehner)
Newsgroups: comp.databases.informix
Subject: conditional select sum() based on field question
Keywords: select sum
Message-ID: <[e-mail]>
Date: 12 Aug 92 15:55:47 GMT
Organization: Praeda Management Systems, Inc.
Lines: 29


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).


   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.

============================================================================
Pete Frehner                 Voice [phone]
Praeda Management Systems                Fax   [phone]
371 Dufferin Ave.                        uucp: .....uunet!praeda!pfrehner
London, Ontario CANADA N6B 1Z5