CODEWITHSUNDEEP
sqlsql-serversql-server-2005mysql
nrk
nrk

Reputation: 315

SQL to find list of stores in city X that carry product Y and order by the total num of orders in asc

I've got these tables:

Stores (id, name, city)
Products (id, name, price)
StoresProducts (id, store_id, product_id)
Orders (id, customer_id, product_id, store_id, order_date, status)

What would be the SQL statement to come up with:

Upvotes: 0

Views: 250

Answers (3)

Jon Watte
Jon Watte

Reputation: 7228

Joining store and products to get stores-carrying-products-in-city is like a text-book example of joining. Counting orders while grouping by store and ordering by count is a very common operation in report writing.

If you're doing homework, I gave you at least as much help as a TA would give you. If this is for work, then you should really think hard about your current situation :-) This query should take at most a minute for anyone who knows SQL to write and test, and I think you'd learn much more by working it out yourself based on these hints than just getting a canned answer, so I'm trying to be as helpful as I can be!

Finally, why did you tag this both "sql server" and "mysql" ? Those are two very different SQL implementations.

Upvotes: 1

shamazing
shamazing

Reputation: 730

select
 s.name, s.city, count(o.id) as numoforders
from
 stores s join
 storesproducts sp on sp.store_id = s.id join
 products p on p.id = sp.product_id join
 order o on o.store_id = o.store_id
where
 s.city = @city and p.id = @productid
group by
 s.name, s.city
order by
 3 desc

Upvotes: 0

ta.speot.is
ta.speot.is

Reputation: 27214

Try this.

SELECT Stores.name
  FROM Stores
       INNER JOIN StoresProducts
         ON Stores.id = StoresProduct.store_id
       INNER JOIN Products
         ON StoresProduct.product_id = Products.id
       INNER JOIN Orders
         ON Stores.id = Orders.id
 WHERE Stores.city = 'New York'
       AND Products.name = 'Shampoo'
 GROUP BY Stores.name
 ORDER BY COUNT(*);

You just ask for the "total number of orders", not "total number of orders of product Y". If you need that, add an extra predicate to the join on Orders.

Upvotes: 0

Related Questions