<pre>The trans table contains the stub for each transaction and the trans_item table contains all the items belonging
tothe transaction. I need to be able to pull categorized reports for items and have all of the totals less the
discountsmatch up with the total from the stubs for a given period. Why is my discount total different when I left
jointhe 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
timezone | default now()trans_type_id | integer | not nullsubtotal | numeric(6,2)
| default 0.00tax | numeric(6,2) | default 0.00discount | numeric(6,2) |
default0.00total_cash | numeric(6,2) | default 0.00total_credit | numeric(6,2) |
default0.00total_check | numeric(6,2) | default 0.00total_gift | numeric(6,2) |
default0.00
mg=# \d trans_item Table "public.trans_item" Column | Type |
Modifiers
------------+--------------+------------------------------------------------------------id | integer | not
nulldefault nextval('public.trans_item_id_seq'::text)trans_id | integer | not nullparent | integer |qty
| integer | not null default 1item_sku | text | not nullitem_price | numeric(5,2) |item_tax |
numeric(4,4)|
</pre>
