pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedaggregate / group by question
3+ messages / 2 participants
[nested] [flat]
* aggregate / group by question
@ 2005-02-22 20:14 T E Schmitz <mailreg@numerixtechnology.de>
2005-02-24 03:56 ` Re: aggregate / group by question Keith Worthington <KeithW@NarrowPathInc.com>
0 siblings, 1 reply; 3+ messages in thread
From: T E Schmitz @ 2005-02-22 20:14 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: aggregate / group by question
2005-02-22 20:14 aggregate / group by question T E Schmitz <mailreg@numerixtechnology.de>
@ 2005-02-24 03:56 ` Keith Worthington <KeithW@NarrowPathInc.com>
2005-02-24 10:34 ` Re: aggregate / group by question T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 1 reply; 3+ messages in thread
From: Keith Worthington @ 2005-02-24 03:56 UTC (permalink / raw)
To: mailreg@numerixtechnology.de; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: aggregate / group by question
2005-02-22 20:14 aggregate / group by question T E Schmitz <mailreg@numerixtechnology.de>
2005-02-24 03:56 ` Re: aggregate / group by question Keith Worthington <KeithW@NarrowPathInc.com>
@ 2005-02-24 10:34 ` T E Schmitz <mailreg@numerixtechnology.de>
0 siblings, 0 replies; 3+ messages in thread
From: T E Schmitz @ 2005-02-24 10:34 UTC (permalink / raw)
To: KeithW@narrowpathinc.com; +Cc: pgsql-sql
Hello Keith,
Thank you for your help.
Keith Worthington wrote:
> T E Schmitz wrote:
>> 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)
>>
>>
> 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
correct
> losing the 'IS NULL' from you retail_price case may fix your statement.
no
> This may be a bit heavy handed AND I am still a novice
that makes two of us ;-)
It worked after a couple of minor changes!
I didn't realize that the select_list can be "made up" from a sub-select.
> SELECT merged_data.payment_method,
> merged_data.category,
> merged_data.subtotal
> FROM (
> -- Get the refunds. (kind = 'R')
> SELECT transaktion.payment_method,
SELECT transaktion.payment_method as 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
sum( item.retail_price ) 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;
---------------------------------------------
In the meantime I had come up with a solution, too - I compared the two
queries with EXPLAIN ANALYZE and my one takes about 4x longer. I haven't
got much data in the test DB yet but the over time the amount of
TRANSAKTIONs, which are never deleted, will be huge:
Here's my version (to reduce complexity I had omitted some details such
as TRANSAKTION.THE_TIME" and ITEM.QUANTITY
select distinct METHOD,
case
when KIND ='R' then 'REFUND'
when KIND ='S' and DISCOUNT is null then 'SALES'
when KIND ='S' and DISCOUNT is not null then 'DISCOUNT'
end as CATEGORY,
(select
sum(
case
when TRANSAKTION.KIND ='R' then
(-(S.RETAIL_PRICE-coalesce(S.DISCOUNT,0))*S.QUANTITY)
when TRANSAKTION.KIND ='S' and ITEM.DISCOUNT is null then
(S.RETAIL_PRICE*S.QUANTITY)
when TRANSAKTION.KIND ='S' and ITEM.DISCOUNT is not null then
(-S.DISCOUNT*S.QUANTITY)
end
)
from ITEM S
inner join TRANSAKTION T on T.TRANSAKTION_PK =S.TRANSAKTION_FK
where
T.THE_TIME >= '1999-01-08' and T.THE_TIME < '2005-02-19' -- this Z-Report
and T.METHOD = TRANSAKTION.METHOD
and T.KIND=TRANSAKTION.KIND
)
as SUBTOTAL
from ITEM
inner join TRANSAKTION on TRANSAKTION_PK =TRANSAKTION_FK
where THE_TIME >= '1999-01-08' and THE_TIME < '2005-02-19'
group by METHOD,KIND,DISCOUNT,QUANTITY
order by METHOD, CATEGORY
--
Regards/Gruß,
Tarlika Elisabeth Schmitz
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-02-24 10:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-22 20:14 aggregate / group by question T E Schmitz <mailreg@numerixtechnology.de>
2005-02-24 03:56 ` Keith Worthington <KeithW@NarrowPathInc.com>
2005-02-24 10:34 ` T E Schmitz <mailreg@numerixtechnology.de>
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