From: Keith Worthington <KeithW@NarrowPathInc.com>
To: mailreg@numerixtechnology.de
Cc: pgsql-sql@postgresql.org
Subject: Re: aggregate / group by question
Date: Wed, 23 Feb 2005 22:56:30 -0500
Message-ID: <421D506E.9050806@NarrowPathInc.com> (raw)
In-Reply-To: <421B92A0.3050506@numerixtechnology.de>
References: <421B92A0.3050506@numerixtechnology.de>
T E Schmitz wrote:
> 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
>
Your comment implies that the amount of retail sales is the sum of all
amounts regardless of whether or not discount IS NULL. So perhaps
losing the 'IS NULL' from you retail_price case may fix your statement.
If not...
This may be a bit heavy handed AND I am still a novice AND I am not on
my system so I can't test it but how about
SELECT merged_data.payment_method,
merged_data.category,
merged_data.subtotal
FROM (
-- Get the refunds. (kind = 'R')
SELECT transaktion.payment_method,
'refunds' AS category,
-1 * sum( item.retail_price - COALESCE(item.discount) )
AS subtotal
FROM transaktion
LEFT OUTER JOIN item
ON ( transaktion.transaktion_pk = item.transaktion_fk )
WHERE transaktion.kind = 'R'
GROUP BY transaktion.payment_method
UNION ALL
-- Get the sales. (kind = 'S')
SELECT transaktion.payment_method,
'sales' AS category,
sum( item.retail_price - COALESCE(item.discount, 0) ) AS
subtotal
FROM transaktion
LEFT OUTER JOIN item
ON ( transaktion.transaktion_pk = item.transaktion_fk )
WHERE transaktion.kind = 'S'
GROUP BY transaktion.payment_method
UNION ALL
-- Get the discounts. (kind = 'S' AND discount IS NOT NULL)
SELECT transaktion.payment_method,
'discounts' AS category,
-1 * sum( COALESCE(item.discount, 0) ) AS subtotal
FROM transaktion
LEFT OUTER JOIN item
ON ( transaktion.transaktion_pk = item.transaktion_fk )
WHERE transaktion.kind = 'S'
AND transaktion.discount IS NOT NULL
GROUP BY transaktion.payment_method
) AS merged_data
ORDER BY merged_data.payment_method,
merged_data.category;
--
HTH
Kind Regards,
Keith
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: KeithW@NarrowPathInc.com, mailreg@numerixtechnology.de
Subject: Re: aggregate / group by question
In-Reply-To: <421D506E.9050806@NarrowPathInc.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