Im creating database for a school project and i need to write code for a couple queries. i have an issue with the one that suppose to show total revenue for the specific period of time.in my case: total revenue=total sales-total refunds. i have 4 tables: sales, sale_details, refunds, refund_details. date details are in sales/refunds tables, everything else that i need is in sale/refund_details table. Thats what i have so far:
total sales
SELECT sale_id, sum(sale_price) as total_sales, sale_date
FROM sale_details JOIN sales USING (sale_id)
WHERE sale_date > DATE_SUB(NOW(), INTERVAL 28 day)
group by sale_id with rollup
total refunds
select refund_id, sum(refund_price) as total_refunds, refund_date
FROM refund_details JOIN refunds USING (refund_id)
WHERE refund_date > DATE_SUB(NOW(), INTERVAL 28 day)
group by refund_id with rollup
Is there any way to combine those queries into one??? total sales minus total refunds
i was trying to complete the query in other way also(see below) but i dont know how to add the date or specific period of time to the query because date details are in separate tables in my database:
select(select sum(sale_price) from sale_details) as total_sale,
(select sum(refund_price) from refund_details) as total_refund,
(select sum(sale_price) from sale_details) - (select sum(refund_price) from refund_details) as total_revenue from dual;
i could move date details from those separate tables to the sales/refund_details tables but im not sure if it is a good solution so i would really appreciate your advice.My basic sql skills are useless here :/ thank you Camille
PS. ERD diagram erd
