SQL Top Selling Product in Each Store GROUP BY EXAMPLE

Given the above tables definition, we would like to find top selling products in each store

Solution:

Here we have to join three tables to get the result

Since the relationship between a product and and store is one-to-many relationship meaning one store has several products but a specific product is stored in a specific store for this organization.

And when we want data from both tables, we have to connect the two tables together as follows 

 select * from product p inner join store s on p.storeid = s.storeid

To get actual products that are sold in each store, we need to join with the orders table 

Here let us find which items are sold from which stores with how much price and quantity:

   select p.prodid, s.StoreId,  p.price , o.quantity , p.price * o.quantity  totalprice

  from orders o  

  inner join product p on p.prodid = o.prodid  

  inner join Store s on s.storeid = p.storeid  

As you can see here, there is repetition of products as multiple customer can purchase the same product from a given store

No we can leverage the power of “group by” to see what are the top selling products in each store.

 

select s.storeid, max(p.price * o.quantity) TotalPrice

from orders o  

inner join product p on p.prodid = o.prodid  

inner join Store s on s.storeid = p.storeid  

group by s.storeid

Now the final step is to use subqueries to get the details of the above rows. We want to know the best selling product in each store

 select s.storeid, p.prodid,p.price , o.quantity quantity, p.price * o.quantity totalprice

from orders o  

inner join product p on p.prodid = o.prodid  

inner join Store s on s.storeid = p.storeid  

where ( p.price * o.quantity) in (

  select   max(p.price * o.quantity) TotalPrice

  from orders o  

  inner join product p on p.prodid = o.prodid  

  inner join Store s on s.storeid = p.storeid  

  group by s.storeid

)