From: Roy Souther <roy@silicontao.com>
To: pgsql-sql@postgresql.org
Subject: Bad SUM result
Date: Sun, 7 Jul 2002 14:25:51 -0600
Message-ID: <200207071425.56235.roy@silicontao.com> (raw)
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I have an invoice database that has two tables one for invoices and one for
payments. I want to get the account balance for a client by subtracting the
sum of all payments from the sum off all invoices for that client.
Here is the SQL that I thought should work.
SELECT SUM(t0.totalprice)-SUM(t1.paymentamount) FROM invoices t0, payments t1
WHERE t0.custnumber='1' AND t1.custnumber='1'
It works fine if there is only one invoice and one payment but as soon as
there is more then one of either it screws up. For each match found in
payments the invoice sum is added to the total. So if client 1 purchased a
$100 item then maid a $10 payment the SQL would return the balance of $90
just fine. When the client makes a second payment of $15 the balance is $75
but this SQL returns ($100+$100)-($10+$15) = $175. A third payment of $1
would return ($100+$100+$100)-($10+$15+$1) = $274.
Could some one explain this to me and recommend an SQL command that would work
please? I could do this using a temp table but that would be very messy as I
would really like it to be a single SQL command.
- --
Roy Souther <roy@SiliconTao.com>
http://www.SiliconTao.com
Linux: May the source be with you.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org
iEYEARECAAYFAj0oo9MACgkQCbnxcmEBt43qFQCgtjCs7khKGH+2LYd78O9mA3h4
vDQAn0GkKkuYl1Kybgm/ITO4LbO1WWLX
=1G4R
-----END PGP SIGNATURE-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: roy@silicontao.com
Subject: Re: Bad SUM result
In-Reply-To: <200207071425.56235.roy@silicontao.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox