--GROUP FUNCTIONS -- 91 rows: SELECT * FROM Customers -- 3 ways of COUNT: -- 1. COUNT(*) - All the rows in the group: SELECT COUNT (*) FROM Customers -- 2. COUNT(column-name) - All the NOT NULL values in the column SELECT COUNT (Region) FROM Customers -- Similar to: SELECT region FROM customers WHERE region IS NOT NULL -- If I want DISTINCT values: SELECT DISTINCT region FROM customers WHERE region IS NOT NULL -- If I just want to count the distinct regions: SELECT COUNT (DISTINCT Region) FROM Customers -- Receive a group of rows (values) and returns one result for the whole group SELECT SUM(UnitPrice), AVG(UnitPrice), MIN(UnitPrice), MAX(UnitPrice) FROM Products SELECT SUM(UnitPrice), AVG(UnitPrice), MIN(UnitPrice), MAX(UnitPrice) FROM Products WHERE ProductID between 1 and 5 SELECT MIN(BirthDate), MAX(BirthDate) FROM Employees SELECT MIN(LastName), MAX(LastName) FROM Employees -- Will not work... SELECT AVG(LastName) FROM Employees -- Calcuate the average price: SELECT COUNT(*), COUNT(UnitPrice) FROM PRODUCTS -- AVG - Only those who have a price -- SUM / COUNT(*) - Including those who do not have a price SELECT AVG(Unitprice), SUM(unitprice)/COUNT(*) FROM products -- Another way to include NULLs as 0 - ISNULL: SELECT AVG( ISNULL(Unitprice,0) ) FROM products -- Groups of rows: -- Grouping with DISTINCT - No information about the groups SELECT DISTINCT region FROM customers -- Grouping with GROUP BY SELECT region FROM customers GROUP BY region -- Now I can use GROUP FUNCTIONS on the groups created: SELECT region, COUNT(*) FROM customers GROUP BY region -- Average price per category SELECT categoryid, AVG(unitprice) AVG, COUNT(*) COUNT, SUM(unitprice) FROM products GROUP BY categoryid -- How many orders for each employee: SELECT Employeeid, COUNT(*) FROM orders GROUP BY Employeeid -- How many orders for each employee per year -- Returns error: SELECT Employeeid, YEAR(orderdate), COUNT(*) FROM orders GROUP BY Employeeid -- Employeeid per year SELECT DISTINCT Employeeid, YEAR(orderdate) FROM orders -- How many orders for each employee per year SELECT Employeeid, YEAR(orderdate), COUNT(*) FROM orders GROUP BY Employeeid, YEAR(orderdate) ORDER BY employeeid -- To restrict rows before grouping use the WHERE SELECT Employeeid, YEAR(orderdate), COUNT(*) FROM orders WHERE MONTH(orderdate) > 4 GROUP BY Employeeid, YEAR(orderdate) ORDER BY employeeid -- Another example: SELECT City, COUNT(*) FROM Employees --solution: add a group by clause SELECT City, COUNT(*) FROM Employees GROUP BY City --another column SELECT Country, City, COUNT(*) FROM Employees GROUP BY Country, City SELECT Country, City, COUNT(*) FROM Employees GROUP BY City, Country --add a condition SELECT City, COUNT(*) FROM Employees WHERE LastName LIKE '[A-P]%' GROUP BY City --HAVING -- If I want to see only employees who have more than 50 orders per year -- Will not work: SELECT Employeeid, YEAR(orderdate), COUNT(*) FROM orders WHERE COUNT(*) > 50 GROUP BY Employeeid, YEAR(orderdate) ORDER BY employeeid -- To include conditions on group functions, use the HAVING clause: SELECT Employeeid, YEAR(orderdate), COUNT(*) FROM orders GROUP BY Employeeid, YEAR(orderdate) HAVING COUNT(*) > 50 ORDER BY employeeid --add a condition on a group function in the wehre => error SELECT City, COUNT(*) FROM Employees WHERE COUNT(*) > 2 GROUP BY City --solve it by placing the condidtion in the having SELECT City, COUNT(*) FROM Employees GROUP BY City HAVING COUNT(*) >= 2 --EXAMPLE SELECT City, COUNT(*) 'count' FROM Employees WHERE LastName LIKE '[A-P]%' GROUP BY City HAVING COUNT(*) >= 2 ORDER BY city