From: T E Schmitz <mailreg@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: aggregate / group by question
Date: Tue, 22 Feb 2005 20:14:24 +0000
Message-ID: <421B92A0.3050506@numerixtechnology.de> (raw)
Hello,
I must apologize for not coming up with a more descriptive subject line.
I am struggling with the following query and I am not even sure whether
what I want to achieve is possible at all:
The problem in real-world terms: The DB stores TRANSAKTIONS - which are
either sales or refunds: each TRANSAKTION has n ITEMS related to it,
which contain their RETAIL_PRICE and DISCOUNT. At the end of day, a
total is run up, which should show the sum of refunds, sales and discounts.
Tables:
TRANSAKTION
-----------
KIND ('R' or 'S' for refund or sale)
TRANSAKTION_PK
PAYMENT_METHOD (cheque, cash, CC)
ITEM
----
TRANSAKTION_FK
ITEM_PK
RETAIL_PRICE
DISCOUNT
Desired result set:
PAYMENT_METHOD | category | SUBTOTAL
------------------------------------
Cash | sales | 103,55
Cash | discounts| -0,53
Cash | refunds | -20,99
CC | sales | 203,55
CC | discounts| -5,53
CC | refunds | -25,99
where
sales amount is the sum of RETAIL_PRICE
discount amount is the sum of DISCOUNT
refunds is the sum of (RETAIL_PRICE-DISCOUNT)
I've had a stab at it but my sales amount is short of the RETAIL_PRICEs
of all discounted ITEMs:
select PAYMENT_METHOD,
case
when KIND='R' then 'R'
when KIND='S' and DISCOUNT is not null then 'D'
when KIND='S' and DISCOUNT is null then 'S'
end as CATEGORY,
sum(case
when KIND=2 then -(RETAIL_PRICE-coalesce(DISCOUNT,0))
when KIND=1 and DISCOUNT is not null then -DISCOUNT
when KIND=1 and DISCOUNT is null then RETAIL_PRICE
end) as SUBTOTAL,
from ITEM
inner join TRANSAKTION on TRANSAKTION_PK =TRANSAKTION_FK
where ...
group by PAYMENT_METHOD,CATEGORY
order by PAYMENT_METHOD,CATEGORY
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
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: mailreg@numerixtechnology.de
Subject: Re: aggregate / group by question
In-Reply-To: <421B92A0.3050506@numerixtechnology.de>
* 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