Search code examples
sqlsql-servernorthwind

Northwind SQL total orders per customer

I've added a column TotalOrders to the Customers table.

ALTER TABLE customers ADD TotalOrders INT NULL

I'm trying to find the total number of orders per customer and add that value to this column, however i can't figure out what do i need to sum exactly

INSERT INTO customers (TotalOrders) SELECT SUM(...)
Solution

  • I think you should be using an update here. You can aggregate the total number of orders per customer in the orders table, and then update the customers table with this information.

    UPDATE t1
SET TotalOrders = t2.TotalOrders
FROM customers t1
INNER JOIN
(
    SELECT CustomerID, COUNT(*) AS TotalOrders
    FROM orders
    GROUP BY CustomerID
) t2
    ON t1.CustomerID = t2.CustomerID