SUM not matching up on a JOIN

From: Tyler Kellen <sleekcode(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: SUM not matching up on a JOIN
Date: 2005-10-26 17:48:35
Message-ID: edc66ef00510261048w345cfa9fp13c69b3fc33dd301@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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) |

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message chester c young 2005-10-26 18:06:32 Re: broken join optimization? (8.0)
Previous Message Tom Lane 2005-10-26 16:39:12 Re: broken join optimization? (8.0)