October 19, 2006 at 5:29 pm
Hi,
I need help with this select query. I am working on this report on refunds issued to the customers.
This is the orderproducts table, products ordered by the customers.
OrderProducts {orderid, productid, price}
There are two tables for refunds - refundscc and refundscheck.
RefundsCC {orderid, productid, refundccid, refundamount}
RefundsCheck {orderid, productid, refundckid, refundamount}
We can ignore the orders table and the master products table. Refunds are issued against a product ordered. A product can have more than one refund and more than one form of refund. The report should display only the orders/products which has refunds issued. The refund total is the sum of refundamount from both the refunds table for that orderid/productid.
Order ID | Product ID | Price Paid | Refund Issued
Any suggestions?
Thanks.
October 19, 2006 at 5:30 pm
Price Paid is the price from the orderproducts table. And Refund Issued is the total refund.
October 20, 2006 at 2:46 am
Since the two Refund tables seem to have the same structure, you could probably use UNION ALL on Refund tables, join the result to orderproducts and do GROUP BY / SUM.
SELECT op.orderid, op.productid, op.price, SUM(Q.refundamount)
FROM orderproducts op
JOIN (select orderid, productid, refundamount
from refundscc
UNION ALL
select orderid, productid, refundamount
from refundscheck) as Q
ON Q.orderid = op.orderid AND Q.productid=op.productid
GROUP BY op.orderid, op.productid, op.price
ORDER BY op.orderid, op.productid, op.price
Supposing that you only want to show orders with refunds, and separately for each orderline. Easy to modify with LEFT JOIN to show all orders, or by skipping the productID and adding SUM on op.price if you want to have sums per order.
October 20, 2006 at 9:25 am
Thank you. I will try that.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply