Re: aggregate / group by question

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