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?
Best Answer
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: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