Question 168414 in Mysql Question posted in
The official MySQL documentation can be found here.

Which id has the highest count in a MySQL table?

I have these tables here:

enter image description here

and I want to query the store ID with the most purchases in a specific month (any month).
I came up with this query:

SELECT DISTINCT store_id FROM (SELECT store_id, purchase_date FROM purchase where purchase_date >= '2021-04-01' AND purchase_date <= '2021-04-30'

SELECT DISTINCT store)

I am still starting in SQL and I can’t seem to move from here.

Tags:

2

Answers


  1. 0

    you can try this

    select count(store_id) as counted_store,store_id from purchase where MONTH(purchase_date) = '5' GROUP BY counted_store ORDER BY counted_store DESC limit 1
    Login or Signup to reply.
  2. 0

    Assuming that most purchases means highest sales and not the number of sales and purchase_date is not text but of type date:

    select x.store_id, sum(x.total) total from (    
   select p.store_id, (pi.price*pi.quantity) total from purchase_item pi
   left join purchase p     on pi.purchase_id=p.id  
   where p.purchase_date between '2021-04-01' AND '2021-04-30' 
) x 
group by x.store_id 
order by total desc 
limit 1;

    If you want to just find store with maximum number of purchases and not the Total value of sales:

    select p.store_id, count(p.store_id) from purchase p
where p.purchase_date between '2021-04-01' AND '2021-04-30' 
group by p.store_id
order by count(p.store_id) desc limit 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search