laika222
10/28/2016 - 7:36 PM

Shows how to select COUNT and SUM of the contents of certain columns. Line 1 selects columns from the Customers table, as well as the COUNT

Shows how to select COUNT and SUM of the contents of certain columns. Line 1 selects columns from the Customers table, as well as the COUNT of the orders in the OrderID column of the Orders table, and the SUM of the Price column from the Orders table. Line 5 groups everything by Customer ID. The result is that you get a line for each CustomerID with the LastName and FirstName, as well as a COUNT of all of that customer's orders, and a SUM of the value of all of the customers orders.

SUM() and Count() SS & MS.sql
content_copyfile_download
SELECT Customers.CustomerID, Customers.LastName, Customers.FirstName, COUNT(Orders.OrderID) AS 'No. of Orders', SUM(Orders.Price) AS 'Total of Purchases'
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
GROUP BY Orders.CustomerID; 

-- You can count the rows in a table by using COUNT(1)
SELECT COUNT(1) AS 'Number of Rows in Customers Table' FROM Customers