📎 This article includes 2 downloadable practice files ↓

📘 SQL Beginner Course · Lesson 4 of 12

“How many orders?” “Total quantity?” “Average price?” Aggregate functions turn many rows into one answer, like SUM and COUNT in Excel.

The five

SELECT COUNT(*)              AS orders     FROM orders;
SELECT SUM(qty)              AS total_qty  FROM orders WHERE status = 'Delivered';
SELECT ROUND(AVG(price), 2)  AS avg_price  FROM products;
SELECT MIN(price) AS cheapest, MAX(price) AS dearest FROM products;
SELECT MIN(order_date) AS first_order, MAX(order_date) AS last_order FROM orders;

MIN and MAX work on dates and text too (earliest date, first name alphabetically).

COUNT(*) vs COUNT(column)

In shop.db, two customers have no GSTIN, so COUNT(gstin) is two less than COUNT(*). Same idea as COUNTA vs COUNTBLANK in Excel.

WHERE runs first

WHERE filters rows before they’re summarised: SUM(qty) … WHERE status = 'Delivered' adds only delivered orders.

NULLs

SUM, AVG, MIN and MAX ignore NULLs. AVG of (10, NULL, 20) is 15, not 10. If a NULL should count as zero, use AVG(COALESCE(col, 0)).

Integer division

In SQLite and SQL Server, 7 / 2 is 3 (both integers). Write 7 * 1.0 / 2 or 100.0 * a / b to get decimals. This bites percentage calculations.

You can’t mix rows and totals… yet

SELECT name, SUM(qty) FROM … without GROUP BY is an error in most databases (SQLite allows it but returns a random name). Lesson 5’s GROUP BY fixes this.

Where beginners go wrong

Practice

Download shop.db and this lesson’s exercise file below. Open the database in the free DB Browser for SQLite (or sqlite3 shop.db), paste the exercises into the Execute SQL tab and write a query for each task. The expected result is printed under every task; answers are at the bottom of the file.