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.
