MySQL for loop to show grouped results

Question

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?

Answer 1

As @Rick James mentioned, a GROUP BY function should do the trick here, and you can use This SO Answer to limit it to the last 12 months. For your example, the query would look something like this:

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` >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
-- AND `user_id` IN (
--  SELECT `id` FROM `users` WHERE `created_at` >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
-- )
GROUP BY MONTH(`created_at`);

NOTE: I commented the subquery for user_id because it would mean the results were only for those customers who both created an account and made a purchase in the last year, and I wasn't sure if that was your desired result. If so, just uncomment it. I edited the subquery so that it should work in that manner