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;

Serkan Arslan · Accepted Answer · 2017-11-23 06:21:26Z

1

You can try this.

SELECT Orders.OrderDate,
       Orders.OrderID,
       Customers.CustomerName,
       SUM(OrderDetails.Quantity) AS "Deliverd products"
FROM Orders
INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
INNER JOIN Orderdetails ON Orders.OrderID = OrderDetails.OrderID
GROUP BY Orders.OrderDate,
       Orders.OrderID,
       Customers.CustomerName
ORDER BY CustomerName;

answered Nov 23, 2017 at 6:21

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Oskar Ingemansson Over a year ago

It worked, is there a reason why you are grouping on OrderDate and CustomerName? I just grouped on OrderID.

Serkan Arslan Over a year ago

You have to group all columns that used in select except used in the aggregate functions.

Ajay2707 · Accepted Answer · 2017-11-23 06:51:05Z

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;

Collectives™ on Stack Overflow

How to join and sum using SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related