# Retrieving data by considering a value from another table

## Issue

Some Context:

I am trying to display all the items with the highest quantity in another table.

I want to display the best-selling items sorted by ‘quantity’ in the OrderItems table. A productId with a quantity of ‘5’ should come first, then the product quantity of ‘4’..so on and so forth.

I am only able to retrieve the items that matched the productId, size, and color, but am stuck on how to factor in the quantity.

OrderItems Table

orderItemsId orderId productId quantity size colors
1 48 1 1 small black
2 48 2 2 small black
3 48 3 5 small black

Product Table

productindex productId name
1 1 Addidas
2 2 Nike
3 3 NewBalance

So, the items displayed should be productId 3,2,1

I just need to sum all the productId quantity, regardless of size and color. and is it to retrieve from the products table by ascending order

This is a small code, just to retrieve the products that happen to be in the orderItems table.

``````SELECT * from Product
inner join OrderItems ON Product.productId = OrderItems.productId where Product.size = OrderItems.size and Product.color = OrderItems.colors
``````

## Solution

You can order by sum():

``````SELECT Product.* from Product inner join OrderItems
ON Product.productId = OrderItems.productId AND
Product.size = OrderItems.size and Product.color = OrderItems.colors
GROUP BY Product.productId
ORDER BY sum(OrderItems.quantity) DESC;
``````

Answered By – walkerlala

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0