fbpx

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'

UpScale Analytics is one of the largest platforms in the world for learning SQL by doing, consisting over 300 SQL exercises at different levels (including solutions), by topics, across over 100 different datasets. More…