Message-ID: <421B92A0.3050506@numerixtechnology.de>
Date: Tue, 22 Feb 2005 20:14:24 +0000
From: T E Schmitz <mailreg@numerixtechnology.de>
Reply-To: mailreg@numerixtechnology.de
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: aggregate / group by question
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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