pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBad SUM result
4+ messages / 4 participants
[nested] [flat]
* Bad SUM result
@ 2002-07-07 20:25 Roy Souther <roy@silicontao.com>
2002-07-07 20:44 ` Re: Bad SUM result Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-08 15:22 ` Re: Bad SUM result Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 2 replies; 4+ messages in thread
From: Roy Souther @ 2002-07-07 20:25 UTC (permalink / raw)
To: pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bad SUM result
2002-07-07 20:25 Bad SUM result Roy Souther <roy@silicontao.com>
@ 2002-07-07 20:44 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
1 sibling, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2002-07-07 20:44 UTC (permalink / raw)
To: Roy Souther <roy@silicontao.com>; +Cc: pgsql-sql
On Sun, 7 Jul 2002, Roy Souther wrote:
> 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.
Right, because you're doing a join that ends up with
100 | 10
100 | 15
100 | 1
as the effective rows.
> 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.
Maybe something like:
select sum(amt) from (select custnumber, totalprice as amt from
invoices union select custnumber, -paymentamount as amt from payments) as
c where custnumber='1';
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bad SUM result
2002-07-07 20:25 Bad SUM result Roy Souther <roy@silicontao.com>
@ 2002-07-08 15:22 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-07-10 08:00 ` Re: Bad SUM result Petr Jezek <Petr.Jezek@i.cz>
1 sibling, 1 reply; 4+ messages in thread
From: Jean-Luc Lachance @ 2002-07-08 15:22 UTC (permalink / raw)
To: Roy Souther <roy@silicontao.com>; +Cc: pgsql-sql
That is because your query is generating a cartesian product.
Try:
SELECT (
SELECT SUM(totalprice)
FROM invoices
WHERE custnumber = '1'
) - (
SELECT SUM(paymentamount)
FROM payments
WHERE custnumber = '1'
)
Roy Souther wrote:
>
> -----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-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Bad SUM result
2002-07-07 20:25 Bad SUM result Roy Souther <roy@silicontao.com>
2002-07-08 15:22 ` Re: Bad SUM result Jean-Luc Lachance <jllachan@nsd.ca>
@ 2002-07-10 08:00 ` Petr Jezek <Petr.Jezek@i.cz>
0 siblings, 0 replies; 4+ messages in thread
From: Petr Jezek @ 2002-07-10 08:00 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
There're no another ways? It don't looks like optimal.
Petr Jezek
----- Original Message -----
From: "Jean-Luc Lachance" <jllachan@nsd.ca>
To: "Roy Souther" <roy@silicontao.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, July 08, 2002 5:22 PM
Subject: Re: [SQL] Bad SUM result
> That is because your query is generating a cartesian product.
>
> Try:
>
> SELECT (
> SELECT SUM(totalprice)
> FROM invoices
> WHERE custnumber = '1'
> ) - (
> SELECT SUM(paymentamount)
> FROM payments
> WHERE custnumber = '1'
> )
>
>
>
> Roy Souther wrote:
> >
> > -----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-----
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-07-10 08:00 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-07 20:25 Bad SUM result Roy Souther <roy@silicontao.com>
2002-07-07 20:44 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-07-08 15:22 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-07-10 08:00 ` Petr Jezek <Petr.Jezek@i.cz>
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