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

Internet Message Format | 1992-08-15 | 2.1 KB

Path: sparky!uunet!cs.utexas.edu!sun-barr!news2me.ebay.sun.com!exodus.Eng.Sun.COM!sun!amdcad!weitek!pyramid!infmx!aland
From: [e-mail] (Colonel Panic)
Newsgroups: comp.databases.informix
Subject: Re: conditional select sum() based on field question
Keywords: select sum
Message-ID: <[e-mail]>
Date: 15 Aug 92 03:03:44 GMT
References: <[e-mail]>
Sender: [e-mail] (Usenet News)
Organization: The Helen Keller Bondurant High Performance Driving School for the Blind
Lines: 43

In article <[e-mail]> [e-mail] (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 [phone]
>Praeda Management Systems                Fax   [phone]
>371 Dufferin Ave.                        uucp: .....uunet!praeda!pfrehner
>London, Ontario CANADA N6B 1Z5


--
Alan Denney      [e-mail]      {pyramid|uunet}!infmx!aland

  Smith and Wesson: the Ultimate Point-and-Click User Interface.