I'm pretty new to sql.

I have the following db schema:

Customers
    CustomerID number
    Name string
    Address string
    Country string
OrderDetails
    OrderDetailID number
    OrderID number
    ProductID number
    Quantity number
Orders
    OrderID number
    CustomerID number
    OrderDate string
    OrderValue string
Products
    ProductID number
    ProductName string
    Price number

I need to get the CustomerID and Quantity for all those that have ordered a particular product name = "oil"

So far I can get the quantity by

select OrderDetails.Quantity
FROM Products
INNER JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID
where Products.ProductName = 'oil'

I can get the CustomerID by following Products.ProductID -> OrderDetails.ProductID -> OrderDetails.OrderID -> Orders.OrderID -> Orders.CustomerID but I am unsure how to express this in sql.

EDIT: I'm looking for a single table like:

 CustomerID | Quantity 
-----------------------
     1          10
     4          40
     5          1

Testing:

I'm using the SQL to regular expression calculator here using the gist here

CodePudding user response：

What you need is to aggregate the quantities per CustomerId.

Since you only require the customerId there's no need to join to the Customer table.

Note also the use of short meaningful aliases makes the query less verbose:

select o.CustomerId, Sum(od.Quantity) Quantity
from Products p
join OrderDetails od on od.ProductID = p.ProductID
join orders o on o.orderid = od.orderid
where p.ProductName = 'oil'
group by o.CustomerId;

CodePudding user response：

select customers.name,sum(OrderDetails.Quantity) as tot_qty
FROM Products
INNER JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID
inner join orders on orderdetails.orderid=orders.orderid
inner join customers on orders.customerid=customers.customerid
where Products.ProductName = 'oil'
group by customers.name

CodePudding user response：

Forget about the distinct, use group by instead.

Use sum() aggregated function to consolidate all orders

select sum(OrderDetails.Quantity) as total, OrderDetails.CustomerID
FROM Products
INNER JOIN OrderDetails ON OrderDetails.ProductID = Products.ProductID
where Products.ProductName = 'oil'
group by OrderDetails.CustomerID