Hi guys, I need help to build a Query that gives me two columns: the date and the profit. The profit would be the “total_sales” - “total_chargeback”.
Note:
(1) approved_at and chargeback_at are dates.
I’m trying this way, but to no avail.
SELECT str_to_date(concat(date_format(`ad_moip`.`approved_at`, '%Y-%m'), '-01'), '%Y-%m-%d') AS 'Mouth_Year', SUM(total_sales - total_chargeback) AS profit
FROM (SELECT
(SELECT SUM(R.value), R.approved_at
FROM ad_moip AS R
WHERE approved_at IS NOT NULL
AND chargeback_at IS NULL
GROUP BY R.approved_at) AS total_sales,
(SELECT SUM(C.value), C.chargeback_at
FROM ad_moip AS C
WHERE chargeback_at IS NOT NULL
GROUP BY C.chargeback_at) AS total_chargeback
)
ORDER BY Mouth_Year ASC
GROUP BY Mouth_Year