🛢️ Unit 2 – Database Query using SQL · Lesson 04
Aggregate Functions, GROUP BY, HAVING
Sample table: SALES
SQL
SELECT * FROM SALES;
Output
+--------+-----------+-------------+--------+------+-------+------------+ | BillNo | Product | Category | Region | Qty | Rate | SaleDate | +--------+-----------+-------------+--------+------+-------+------------+ | 1 | Laptop | Electronics | North | 2 | 52000 | 2025-04-03 | | 2 | Mouse | Electronics | South | 10 | 450 | 2025-04-05 | | 3 | Chair | Furniture | North | 4 | 3200 | 2025-04-11 | | 4 | Table | Furniture | East | 1 | 7800 | 2025-05-02 | | 5 | Printer | Electronics | East | 1 | 13500 | 2025-05-09 | | 6 | Bookshelf | Furniture | South | 2 | 5600 | 2025-05-21 | | 7 | Pen Drive | Electronics | North | 15 | 600 | 2025-06-01 | | 8 | Notebook | Stationery | South | 50 | 60 | NULL | +--------+-----------+-------------+--------+------+-------+------------+
Aggregate functions
Aggregate (multi-row) functions take many rows and give back one value: MAX(), MIN(), AVG(), SUM() and COUNT(). They ignore NULL values.
SQL
SELECT SUM(Qty), MAX(Rate), MIN(Rate), ROUND(AVG(Rate), 2) FROM SALES;
Output
+----------+-----------+-----------+---------------------+ | SUM(Qty) | MAX(Rate) | MIN(Rate) | ROUND(AVG(Rate), 2) | +----------+-----------+-----------+---------------------+ | 85 | 52000 | 60 | 10401.25 | +----------+-----------+-----------+---------------------+
SQL
SELECT SUM(Qty * Rate) AS TotalSales FROM SALES WHERE Category = 'Electronics';
Output
+------------+ | TotalSales | +------------+ | 131000 | +------------+
COUNT variations
COUNT(*)– all rows.COUNT(col)– rows where col is not NULL (SaleDate is NULL for one row).COUNT(DISTINCT col)– different values only.
SQL
SELECT COUNT(*), COUNT(SaleDate), COUNT(DISTINCT Category), COUNT(DISTINCT Region) FROM SALES;
Output
+----------+-----------------+--------------------------+------------------------+ | COUNT(*) | COUNT(SaleDate) | COUNT(DISTINCT Category) | COUNT(DISTINCT Region) | +----------+-----------------+--------------------------+------------------------+ | 8 | 7 | 3 | 3 | +----------+-----------------+--------------------------+------------------------+
GROUP BY
GROUP BY collects rows with the same value into groups so that an aggregate function is calculated for each group.
SQL
SELECT Category, COUNT(*) AS Bills, SUM(Qty * Rate) AS Amount FROM SALES GROUP BY Category ORDER BY Category;
Output
+-------------+-------+--------+ | Category | Bills | Amount | +-------------+-------+--------+ | Electronics | 4 | 131000 | | Furniture | 3 | 31800 | | Stationery | 1 | 3000 | +-------------+-------+--------+
SQL
SELECT Region, MAX(Rate) AS Costliest, MIN(Rate) AS Cheapest FROM SALES GROUP BY Region ORDER BY Region;
Output
+--------+-----------+----------+ | Region | Costliest | Cheapest | +--------+-----------+----------+ | East | 13500 | 7800 | | North | 52000 | 600 | | South | 5600 | 60 | +--------+-----------+----------+
SQL
SELECT MONTHNAME(SaleDate) AS Month, SUM(Qty * Rate) AS Amount FROM SALES WHERE SaleDate IS NOT NULL GROUP BY MONTH(SaleDate), MONTHNAME(SaleDate) ORDER BY MONTH(SaleDate);
Output
+-------+--------+ | Month | Amount | +-------+--------+ | April | 121300 | | May | 32500 | | June | 9000 | +-------+--------+
HAVING
SQL
SELECT Category, SUM(Qty) AS Units FROM SALES GROUP BY Category HAVING SUM(Qty) > 10 ORDER BY Units DESC;
Output
+-------------+-------+ | Category | Units | +-------------+-------+ | Stationery | 50 | | Electronics | 28 | +-------------+-------+
SQL
SELECT Region, COUNT(*) FROM SALES WHERE Category <> 'Stationery' GROUP BY Region HAVING COUNT(*) >= 2 ORDER BY Region;
Output
+--------+----------+ | Region | COUNT(*) | +--------+----------+ | East | 2 | | North | 3 | | South | 2 | +--------+----------+
ORDER BY
SQL
SELECT Product, Qty * Rate AS Amount FROM SALES ORDER BY Amount DESC;
Output
+-----------+--------+ | Product | Amount | +-----------+--------+ | Laptop | 104000 | | Printer | 13500 | | Chair | 12800 | | Bookshelf | 11200 | | Pen Drive | 9000 | | Table | 7800 | | Mouse | 4500 | | Notebook | 3000 | +-----------+--------+
GROUP BY on two columns
SQL
SELECT Category, Region, SUM(Qty) FROM SALES GROUP BY Category, Region ORDER BY Category, Region;
Output
+-------------+--------+----------+ | Category | Region | SUM(Qty) | +-------------+--------+----------+ | Electronics | East | 1 | | Electronics | North | 17 | | Electronics | South | 10 | | Furniture | East | 1 | | Furniture | North | 4 | | Furniture | South | 2 | | Stationery | South | 50 | +-------------+--------+----------+
Rules to remember
- Aggregates skip NULL; only COUNT(*) counts every row.
- Conditions on rows → WHERE; on groups → HAVING.
- Clause order in a query: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY.
Frequently Asked Questions
Can I use an aggregate function in WHERE?
No. WHERE works on single rows before grouping. Put conditions on aggregates in HAVING, e.g. HAVING SUM(Qty) > 10.
Which columns can appear in SELECT with GROUP BY?
The grouped columns and aggregate functions. Other columns have no single value per group.
How do I sort in descending order?
Add DESC after the column in ORDER BY, e.g. ORDER BY Amount DESC. ASC (ascending) is the default.
