How to join and sum using SQL

Question

I got three tables that I'm trying to join together. My goal is to see of each order, its orderdate, orderid, customer and the total amount of delivered items. Each unique item in a order get a separate line in the order details table which means that I need to sum the total amount in my join but I don't know how?

    SELECT Orders.OrderDate
        , Orders.OrderID
        , Customers.CustomerName
        , OrderDetails.Quantity AS "Deliverd products"

    FROM Orders INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
    INNER JOIN Orderdetails ON Orders.OrderID = OrderDetails.OrderID
    ORDER BY CustomerName;

Answer 1

You can try with Sum Over option where you do not need group by explicitly

declare @orders table(orderid int identity (1,1), orderdate datetime, customerid int)
declare @orderdetails table(orderdetailsid int identity (1,1), orderid int, quantity int)
declare @customers table(customerid int identity (1,1), customername varchar(100))

insert into @orders values('2017-11-21' , 1)
insert into @orderdetails values (1,20) ,(1,5) , (1,7)
insert into @customers values('ajay')


SELECT distinct o.OrderDate,
       o.OrderID,
       c.CustomerName

       SUM(od.Quantity) OVER (ORDER BY od.OrderID) 
FROM @orders o
INNER JOIN @customers c ON c.CustomerID = o.CustomerID
INNER JOIN @orderdetails od ON o.OrderID = od.OrderID

ORDER BY CustomerName;