I want to SUM the total sales every month for the past 6 months and * that by the commission percentage. What I have below is working but I also want to calculate the total refunds for the month and then deduct them from the result I get from the below query. That means I would also need to run another SELECT SUM query in here but I don’t know how. The refunds/credit table is called ‘credit’ and the column for the total is ‘credit_value’ and the refund date column is ‘credit_date’.
SELECT year(v.purchased), month(v.purchased), SUM(sales_price * commission) as total FROM vouchers v INNER JOIN commission c ON v.voucher_id = c.voucher_id WHERE v.status = 'Approved' AND v.purchased>=date_sub(curdate(), interval 6 month) GROUP BY year(v.purchased), month(v.purchased) ORDER BY year(v.purchased), month(v.purchased)