Path: utzoo!mnetor!uunet!seismo!sundc!pitstop!sun!amdcad!ames!umd5!uvaarpa!mcnc!gatech!ukma!psuvm.bitnet!uh2
From: UH2@PSUVM.BITNET (Lee Sailer)
Newsgroups: comp.databases
Subject: Re: Informix query
Message-ID: <29265UH2@PSUVM>
Date: 8 Jan 88 14:35:12 GMT
References: <3072@ihlpf.ATT.COM> <3890001@hpcmmb.HP.COM> <3216@ihlpf.ATT.COM> <60@coot.AUSTIN.LOCKHEED.COM>
Organization: Penn Sate Erie--School of Business
Lines: 30

In article <60@coot.AUSTIN.LOCKHEED.COM>, chris@AUSTIN.LOCKHEED.COM (Chris Wood) says:
>
>1. Find customers where qty-ordered > 1000
>2. For each record in 1
>2.1 If customer.area = "NORTHEAST" then
>2.1.1   update region
>   set good-customers = good-customers + 1
>2.2 find orders where orders.custno = customers.number
>2.3 for each record in 2.2
>2.3.1 ... add up the total orders for that customer and store them back
>          into that customers record...
>.
I've never used any SQL product, but supposedly know some of the underlying
ideas.  How about something like
     
good customers = Select count(id#)
                 FROM customers
                 WHERE area = NORTHEAST and qty.ordered > 1000
>
     
For the total orders problem, try something like
     
SELECT number, sum(amount)
FROM customers, orders
WHERE custno = number
GROUP BY number
     
and define customers.totalamt = SELECT tamt FROM t
                                WHERE customers.number=t.number