0

how do i get rows for each month which contain the store with the highest number of purchases?

my code is:

SELECT DATE_FORMAT(buyDate, '%M') as month, count(id) as numPurchases, store
from purchase, customerpurchases
where purchase.id = customerpurchases.purchaseid
group by month, store;

enter image description here

  • In MySQL 8+ use CTE and ROW_NUMBER (or FIRST_VALUE). In MySQL 5+ use subquery to obtain max of purchases number. – Akina Oct 17 at 4:43
  • @Akina, unfortunately when doing a subquery, it counts the total of all numPuchases and lists it in a single row as 3859. Thats why i am confused on how to write a subquery that can fulfill my request. Any suggestions? – JaredNardi26 Oct 17 at 5:00
  • when doing a subquery, it counts the total of all numPuchases and lists it in a single row as 3859. You must select the month, not max value only, and group by the month... then join to table by month and value. – Akina Oct 17 at 5:02
  • @Akina, thank you for your answer, but it doesnt compile in mysql. im still getting a single row of 3859 when i enter in; select numpurchases, DATE_FORMAT(buyDate, '%M') as month, store from (select count(purchaseid) as numpurchases, buydate, store from customerpurchases, purchase where purchase.id = customerpurchases.purchaseid) as a group by buydate; – JaredNardi26 Oct 17 at 5:55
  • Create a sample fiddle with some data (instead of a picture - CREATE TABLE + INSERT INTO) and show desured output for that data, please. – Akina Oct 17 at 6:02
0
SELECT t1.*
FROM purchase t1
JOIN ( SELECT t2.`month`, MAX(t2.numPurchases) numPurchases
       FROM purchase t2
       GROUP BY t2.`month` ) t3 
    ON t1.`month` = t3.`month`
   AND t1.numPurchases = t3.numPurchases

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.