pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSUM not matching up on a JOIN
2+ messages / 2 participants
[nested] [flat]
* SUM not matching up on a JOIN
@ 2005-10-26 17:48 Tyler Kellen <sleekcode@gmail.com>
2005-10-26 20:51 ` Re: SUM not matching up on a JOIN Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Tyler Kellen @ 2005-10-26 17:48 UTC (permalink / raw)
To: pgsql-sql
The trans table contains the stub for each transaction and the
trans_item table contains all the items belonging to the transaction.
I need to be able to pull categorized reports for items and have all
of the totals less the discounts match up with the total from the
stubs for a given period. Why is my discount total different when I
left join the trans table to the totals?
mg=# SELECT SUM(subtotal+tax) AS total, SUM(discount) AS discount FROM
trans WHERE DATE_TRUNC('DAY',stamp)='20051010';
total | discount
--------+----------
438.35 | 9.75
(1 row)
mg=# SELECT sum(item_price+round(item_price*item_tax,2)*qty) as
total, sum(t.discount)
mg-# FROM trans_item ti
mg-# LEFT JOIN trans t
mg-# ON ti.trans_id=t.id
mg-# WHERE date_trunc('day',t.stamp)='20051010';
total | discount
--------+----------
444.10 | 14.52
mg=# \d trans
Table "public.trans"
Column | Type | Modifiers
---------------+-----------------------------+-------------------------------------------------------
id | integer | not null default
nextval('public.trans_id_seq'::text)
stamp | timestamp without time zone | default now()
trans_type_id | integer | not null
subtotal | numeric(6,2) | default 0.00
tax | numeric(6,2) | default 0.00
discount | numeric(6,2) | default 0.00
total_cash | numeric(6,2) | default 0.00
total_credit | numeric(6,2) | default 0.00
total_check | numeric(6,2) | default 0.00
total_gift | numeric(6,2) | default 0.00
mg=# \d trans_item
Table "public.trans_item"
Column | Type | Modifiers
------------+--------------+------------------------------------------------------------
id | integer | not null default
nextval('public.trans_item_id_seq'::text)
trans_id | integer | not null
parent | integer |
qty | integer | not null default 1
item_sku | text | not null
item_price | numeric(5,2) |
item_tax | numeric(4,4) |
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: SUM not matching up on a JOIN
2005-10-26 17:48 SUM not matching up on a JOIN Tyler Kellen <sleekcode@gmail.com>
@ 2005-10-26 20:51 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2005-10-26 20:51 UTC (permalink / raw)
To: Tyler Kellen <sleekcode@gmail.com>; +Cc: pgsql-sql
Tyler Kellen <sleekcode@gmail.com> writes:
> Why is my discount total different when I
> left join the trans table to the totals?
Are you sure that trans_item.trans_id is unique and accounts for all the
existing values of trans.id? I don't see any unique index or foreign
key constraints in your \d output, so the database isn't enforcing that
condition for you ...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-10-26 20:51 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-26 17:48 SUM not matching up on a JOIN Tyler Kellen <sleekcode@gmail.com>
2005-10-26 20:51 ` Tom Lane <tgl@sss.pgh.pa.us>
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