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) |
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) |