X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B8E89DADB7 for ; Wed, 26 Oct 2005 14:48:36 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 06816-09 for ; Wed, 26 Oct 2005 17:48:34 +0000 (GMT) Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.207]) by svr1.postgresql.org (Postfix) with ESMTP id 61F66D9FA1 for ; Wed, 26 Oct 2005 14:48:34 -0300 (ADT) Received: by xproxy.gmail.com with SMTP id t14so389282wxc for ; Wed, 26 Oct 2005 10:48:35 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=s7VO13DndqxLyOUpgTwdk+iYEnci8U7IkHw/L2R59UCrzYjlbtie8ndaAE/euB9lHkL6GrzWIHIGQmTRidmMUutBpdFi3rQY3OtZ87sJut+aKSPzPLyHlMnc1LhznvfdWg9xM9p+JcdnhxeqvD7K/z5Y1kUOgMFQHf95/GW6CMY= Received: by 10.70.35.5 with SMTP id i5mr636626wxi; Wed, 26 Oct 2005 10:48:35 -0700 (PDT) Received: by 10.70.38.13 with HTTP; Wed, 26 Oct 2005 10:48:35 -0700 (PDT) Message-ID: Date: Wed, 26 Oct 2005 12:48:35 -0500 From: Tyler Kellen To: pgsql-sql@postgresql.org Subject: SUM not matching up on a JOIN MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1061_26024867.1130348915170" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.529 required=5 tests=[HTML_20_30=0.504, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200510/340 X-Sequence-Number: 23193 ------=_Part_1061_26024867.1130348915170 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline The trans table contains the stub for each transaction and the trans_item table contains all the items belonging to the transaction.=20 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=3D# SELECT SUM(subtotal+tax) AS total, SUM(discount) AS discount FROM trans WHERE DATE_TRUNC('DAY',stamp)=3D'20051010'; total | discount --------+---------- 438.35 | 9.75 (1 row) mg=3D# 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=3Dt.id mg-# WHERE date_trunc('day',t.stamp)=3D'20051010'; total | discount --------+---------- 444.10 | 14.52 mg=3D# \d trans Table "public.trans" Column | Type | Modifi= ers ---------------+-----------------------------+-----------------------------= -------------------------- 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=3D# \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) | ------=_Part_1061_26024867.1130348915170 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 
The trans table contains the stub for each transaction and the trans_i=
tem table contains all the items belonging to the transaction.  I need to b=
e able to pull categorized reports for items and have all of the totals les=
s 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?
=20
mg=3D# SELECT SUM(subtotal+tax) AS total, SUM(discount) AS discount FROM tr=
ans WHERE DATE_TRUNC('DAY',stamp)=3D'20051010';
 total  | discount
--------+----------
 438.35 |     9.75
(1 row)

mg=3D# 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=3Dt.id
mg-# WHERE     date_trunc('day',t.stamp)=3D'20051010';
 total  | discount
--------+----------
 444.10 |    14.52


mg=3D# \d trans
                                        Table "public.trans"
    Column     |            Type             |                       Modifi=
ers
---------------+-----------------------------+-----------------------------=
--------------------------
 id            | integer                     | not null default nextval('pu=
blic.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
=20
mg=3D# \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) |
------=_Part_1061_26024867.1130348915170--