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