1

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?

1
  • 2
    Hint: GROUP BY MONTH(created_at) – Rick James Jan 11 '18 at 20:54
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

2
  • 1
    Not exactly the same, given how the OP is calculating the month period in their existing query. They appear to want to aggregate data between points 1 month from now and now, then 2 months from now and 1 month from now, then 3 months from and and 2 months from now etc. Simply grouping by MONTH(created_at) as in this solution will give you data by calendar months, which is different. – Andriy M Jan 12 '18 at 18:28
  • Thanks for the clarification, @AndriyM. You are certainly right. I will take another look and see if I can edit the answer to fit. – mtr.web Jan 12 '18 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.