Sales and refunds table

Question

I have a "transactions" MySQL table on which I record all sales and refunds for my products. Each record thus either represents a sale or a refund (indicated by a flag), the transaction id, the transaction date and the value of the transaction. In case of refunds there is also a refund_id which indicates for which transaction id this refund is for.

Can I, using one query only, calculate how many sales were made in a certain time period (a sale is considered if the refund value is less than the sale value) and the overall amount earned (ie sales value minus refund value).

Notes:
1. There can be at most one refund for every sale.
2. In case of refunds the date to keep in mind is not the date when the refund was done (ie what is recorded in the DB for a particula refund), but when the original sale was done

Answer 1

SELECT SUM(numberofsales) as numberofsales,SUM(salevalue)
FROM (
--handle sales with refunds
SELECT count(sales) as numberofsales, sum(sales.value-refunds.value) as salevalue
FROM Transactions sales
LEFT JOIN transactions refunds ON sales.transid=refunds.refundid
WHERE refunds.refundid IS NOT NULL
AND sales.value-refund.value>0
AND sales.date>@begindate
AND sales.date<@enddate
AND sales.refund_flag='s'
UNION
--handle sales without refunds
SELECT count(sales) as numberofsales, sum(sales.value) as salevalue
FROM Transactions sales
LEFT JOIN transactions refunds ON sales.transid=refunds.refundid
WHERE refunds.refundid IS NULL
AND sales.date>@begindate
AND sales.date<@enddate
AND sales.refund_flag='s')

This is a first pass with the major charachteristics of the solution. It's using SQL Server syntax, I'm not sure of the differences between sql server and mysql.

As per michael667's answer above, you can see the self join to get the refunds for each sale. I dont think the group by is required.

This solution does not attempt to ensure there is only one refund for each sale, and indeed if there is more than one, it will affect the corrrectness of this solution.

Answer 2

This can be done using a self join in conjunction with group by.