From: Tyler Kellen <sleekcode@gmail.com>
To: pgsql-sql@postgresql.org
Subject: SUM not matching up on a JOIN
Date: Wed, 26 Oct 2005 12:48:35 -0500
Message-ID: <edc66ef00510261048w345cfa9fp13c69b3fc33dd301@mail.gmail.com> (raw)
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) |
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sleekcode@gmail.com
Subject: Re: SUM not matching up on a JOIN
In-Reply-To: <edc66ef00510261048w345cfa9fp13c69b3fc33dd301@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox