Received: from tech.com.au (IDENT:root@techpt.lnk.telstra.net [139.130.75.122]) by hub.org (8.9.3/8.9.3) with ESMTP id XAA05835 for ; Fri, 2 Jul 1999 23:38:30 -0400 (EDT) (envelope-from chris@tech.com.au) Received: from tech.com.au (tardis [203.41.180.243]) by tech.com.au (8.9.3/8.9.3) with ESMTP id NAA21553 for ; Sat, 3 Jul 1999 13:38:24 +1000 Sender: chris@tech.com.au Message-ID: <377D77F6.8DCFAFC8@tech.com.au> Date: Sat, 03 Jul 1999 12:39:50 +1000 From: Chris Bitmead X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.2.9 i586) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgreSQL.org Subject: Re: [SQL] Tricky SQL (?) References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Use a temporary table. Peter Eisentraut wrote: > > Let's say I have a table of credits with a customer number attached to it > and a table of refunds with a customer number attached to it. Occasionally > I want to go through this list and check if any customers still have > more credits summed up than refunds. > > The technically correct choice for a query would be something like > > SELECT customer_nr, sum(amount) FROM ( SELECT customer_nr, amount FROM > credits UNION customer_nr, -amount FROM refunds ) GROUP BY customer_nr > HAVING sum(amount)>0; > > Unfortunately, this doesn't work because subselects are not allowed in the > target list. The current solution is to read in all credits and refunds > and have the application (some PHP, some Perl) do the summing and > filtering. But this doesn't only seem clumsy but it creates unneccessay > network traffic. > > Seemingly, this should be a common problem, like invoices vs. payments, > assets vs. liabilities, etc. Does anyone have suggestions on how to tackle > this? I am open to changes in the table structure, too. > > -- > Peter Eisentraut > PathWay Computing, Inc. -- Chris Bitmead mailto:chris@tech.com.au http://www.techphoto.org - Photography News, Stuff that Matters