Re: [SQL] aggregate / group by question

[Keith Worthington]

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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly