Re: aggregate / group by question - Mailing list 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