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?