SUM not matching up on a JOIN
От | Tyler Kellen |
---|---|
Тема | SUM not matching up on a JOIN |
Дата | |
Msg-id | edc66ef00510261048w345cfa9fp13c69b3fc33dd301@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: SUM not matching up on a JOIN
|
Список | pgsql-sql |
<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>
В списке pgsql-sql по дате отправления: