I'm trying to show results for 12 months using this query:
SELECT
COUNT(DISTINCT user_id) as customers,
SUM(amount)*.01 as amount,
SUM(amount_refunded)*.01 as refunded,
MONTH(MAX(created_at)) as month
FROM `payments`
WHERE `amount` > 0
AND `status` = 'succeeded'
AND `created_at` BETWEEN NOW() - INTERVAL 2 MONTH AND NOW() - INTERVAL 1 MONTH
AND `user_id` IN (SELECT `id` FROM `users` WHERE `created_at` BETWEEN NOW() - INTERVAL 2 MONTH AND NOW() - INTERVAL 1 MONTH);
Could easily do this programmatically using PHP to output into an HTML table where each row includes shows a different month by incrementing the month and querying the DB again each time.
How would I do that with this query using a MySQL FOR loop instead? Would that even be the right approach?
GROUP BY MONTH(created_at)
– Rick James Jan 11 '18 at 20:54