SQL Server Group Functions Examples

----------------------------
-- Group Functions
----------------------------

----------------------------
-- AVG
----------------------------

SELECT AVG(unitPrice)
FROM products 

-- Error
SELECT AVG(productName)
FROM products
-- Error
SELECT AVG(hireDate)
FROM employees 

----------------------------
-- SUM
----------------------------

SELECT SUM(unitPrice)
FROM products 

-- Error
SELECT SUM(productName)
FROM products
-- Error
SELECT SUM(hireDate)
FROM employees 

----------------------------
-- MIN / MAX
----------------------------

SELECT MIN(unitPrice) , MAX(unitPrice)
FROM products 

SELECT MIN(productName) , MAX(productName)
FROM products 

SELECT MIN(hireDate) , MAX(hireDate)
FROM employees

------------------------
-- COUNT
------------------------

-- 1 COUNT(*) 

SELECT COUNT(*)
FROM employees 

-- 2 COUNT(column) 

SELECT COUNT(employeeID)
FROM employees

SELECT lastName , region
FROM employees  

SELECT COUNT(region)
FROM employees

SELECT COUNT(*) - COUNT(region)
FROM employees 

-- 3. COUNT (DISTINCT column) 

SELECT COUNT(DISTINCT categoryID)
FROM products 

SELECT AVG(unitPrice)
FROM products
WHERE categoryID IN (1,2)  

-------------------------
-- GROUP BY
-------------------------

SELECT AVG(unitPrice) , categoryID
FROM products
GROUP BY categoryID 

SELECT AVG(unitPrice) , categoryID
FROM products
WHERE categoryID IN (1,2,3)
GROUP BY categoryID 

SELECT productName , unitPrice , supplierID
FROM products 

SELECT MAX(unitPrice) ,  supplierID
FROM products
GROUP BY supplierID 

SELECT COUNT(*) AS 'NumOfRow',  supplierID
FROM products
GROUP BY supplierID

SELECT COUNT(*) AS 'NumOfRow',  supplierID , categoryID
FROM products
GROUP BY supplierID , categoryID 

---------------------------------
-- HAVING
---------------------------------

SELECT AVG(unitPrice) , categoryID
FROM products
WHERE categoryID IN (1,2,3)
GROUP BY categoryID 

SELECT AVG(unitPrice) , categoryID
FROM products
GROUP BY categoryID
HAVING AVG(unitPrice) > 60 

SELECT AVG(unitPrice) , categoryID
FROM products
WHERE categoryID = 1
GROUP BY categoryID
HAVING AVG(unitPrice) > 60

SELECT productName , unitPrice AS 'un'
FROM products
WHERE 'un' > 10 

SELECT productName , unitPrice AS 'un'
FROM products
ORDER BY 'un'