Hello Keith, Thank you for your help.
Keith Worthington wrote:
T E Schmitz wrote:Your comment implies that the amount of retail sales is the sum of all amounts regardless of whether or not discount IS NULL. So perhapsTables:
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)
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
---------------------------(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
