GROUP BY, HAVING and ORDER BY in SQL Server

GROUP BY -> make groups (one row per group)
HAVING   -> filter the GROUPS
ORDER BY -> sort the result

WHERE vs HAVING
  WHERE  -> filters ROWS   (before grouping)
  HAVING -> filters GROUPS (after grouping, can use SUM, COUNT ...)

Order of writing a query:
  SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY

This lesson creates its own small tables, so you can run it on any SQL Server database. The Output under each query is the real result.

IF DB_ID('SqlBasicsDb') IS NULL
    CREATE DATABASE SqlBasicsDb;
GO

USE SqlBasicsDb;
GO

DROP TABLE IF EXISTS Staff;
GO

CREATE TABLE Staff
(
    EmpID      INT PRIMARY KEY,
    Name       VARCHAR(50),
    Department VARCHAR(30),
    City       VARCHAR(30),
    Gender     CHAR(1),
    Salary     INT
);

INSERT INTO Staff VALUES
(1,  'Ravi',  'IT',      'Hyderabad', 'M', 50000),
(2,  'Sita',  'HR',      'Chennai',   'F', 40000),
(3,  'John',  'IT',      'Bengaluru', 'M', 60000),
(4,  'Meena', 'Sales',   'Hyderabad', 'F', 35000),
(5,  'Arjun', 'Sales',   'Mumbai',    'M', 45000),
(6,  'Pooja', 'IT',      'Hyderabad', 'F', 55000),
(7,  'Kiran', 'HR',      'Mumbai',    'M', 38000),
(8,  'Divya', 'Finance', 'Chennai',   'F', 52000),
(9,  'Rahul', 'Finance', 'Bengaluru', 'M', 48000),
(10, 'Neha',  'IT',      'Hyderabad', 'F', 65000);
GO

SELECT * FROM Staff;

-- =============================================
-- ORDER BY
-- =============================================

(10 rows affected)

Sort by one column

SELECT Name, Salary FROM Staff ORDER BY Salary;          -- low to high
SELECT Name, Salary FROM Staff ORDER BY Salary DESC;     -- high to low

Sort by two columns (first Department, then Salary inside it)

SELECT Name, Department, Salary
FROM Staff
ORDER BY Department ASC, Salary DESC;

-- =============================================
-- GROUP BY
-- =============================================

Number of employees in each department

SELECT Department, COUNT(*) AS Employees
FROM Staff
GROUP BY Department;

Total and average salary in each department

SELECT Department,
       SUM(Salary) AS TotalSalary,
       AVG(Salary) AS AverageSalary
FROM Staff
GROUP BY Department;

Highest and lowest salary in each city

SELECT City, MAX(Salary) AS Highest, MIN(Salary) AS Lowest
FROM Staff
GROUP BY City;

Group by Two columns (department + gender)

SELECT Department, Gender, COUNT(*) AS Employees
FROM Staff
GROUP BY Department, Gender;

GROUP BY + ORDER BY (biggest department first)

SELECT Department, COUNT(*) AS Employees
FROM Staff
GROUP BY Department
ORDER BY Employees DESC;

WHERE + GROUP BY (only employees from Hyderabad, then group)

SELECT Department, COUNT(*) AS Employees
FROM Staff
WHERE City = 'Hyderabad'
GROUP BY Department;

-- RULE: every column in SELECT must be inside GROUP BY
--       or inside an aggregate function (COUNT, SUM ...).
-- This gives an ERROR because Name is not grouped:
--   SELECT Department, Name, COUNT(*) FROM Staff GROUP BY Department;

-- =============================================
-- HAVING
-- =============================================

Departments with more than 2 employees

SELECT Department, COUNT(*) AS Employees
FROM Staff
GROUP BY Department
HAVING COUNT(*) > 2;

Departments where the average salary is above 45000

SELECT Department, AVG(Salary) AS AverageSalary
FROM Staff
GROUP BY Department
HAVING AVG(Salary) > 45000;

Cities where total salary is at least 1,00,000

SELECT City, SUM(Salary) AS TotalSalary
FROM Staff
GROUP BY City
HAVING SUM(Salary) >= 100000;

-- =============================================
-- ALL TOGETHER : WHERE + GROUP BY + HAVING + ORDER BY
-- =============================================

Among employees earning more than 40000:

show departments with at least 2 such people, highest total salary first

SELECT Department,
       COUNT(*)    AS Employees,
       SUM(Salary) AS TotalSalary
FROM Staff
WHERE Salary > 40000             -- 1. filter rows
GROUP BY Department              -- 2. make groups
HAVING COUNT(*) >= 2             -- 3. filter groups
ORDER BY TotalSalary DESC;       -- 4. sort