Skip to content

Beginner · Databases and SQL basics · topic 20 of 54 · 25 min

Counting and totals: aggregate functions

Explanation

Aggregate functions turn many rows into one answer: COUNT counts rows, SUM adds, AVG finds the average, and MIN and MAX find the smallest and largest. With GROUP BY you get one answer per group, like total sales per city.

Many rows in, one answer out

So far each query returned rows from the table. An aggregate function looks at many rows and returns a single value. The shop owner does not want 10,000 bills; she wants "how many orders today and how much money".

Think of it like this: Think of a cashier closing the counter at night. She does not read out every bill. She counts the bills (COUNT), adds them up (SUM), notes the smallest and biggest bill (MIN, MAX) and the average bill size (AVG). Aggregate functions are that closing summary.
FunctionGivesExample
COUNT(*)number of rowsSELECT COUNT(*) FROM orders
COUNT(column)number of rows where that column is NOT NULLSELECT COUNT(rating) FROM orders
SUM(column)totalSELECT SUM(amount) FROM orders
AVG(column)averageSELECT AVG(amount) FROM orders
MIN / MAXsmallest / largestSELECT MAX(amount) FROM orders
sql
SELECT COUNT(*) AS orders, SUM(amount) AS total
FROM orders
WHERE city = 'Pune';
WHERE runs first, so this counts and adds only Pune orders.

COUNT(*) vs COUNT(column)

COUNT(*) counts every row. COUNT(rating) counts only the rows where rating has a value; rows where it is NULL are skipped. SUM, AVG, MIN and MAX also skip NULLs. So if 8 orders exist and only 5 were rated, COUNT(*) is 8 and COUNT(rating) is 5.

Rounding averages

An average often has many decimals, like 825.625. ROUND(value, 2) keeps 2 digits after the point: 825.63. For money, round to 2 places; for marks, 1 place is often enough.

sql
SELECT ROUND(AVG(amount), 2) AS average_bill FROM orders;
The average order value, rounded to paise.

A first look at GROUP BY

GROUP BY city splits the rows into one group per city and runs the aggregate on each group. You get one result row per city. Every column in SELECT must be either in GROUP BY or inside an aggregate function. You will go deeper into GROUP BY in the next module.

sql
SELECT city, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY city
ORDER BY city;
One row per city: how many orders and how much money.
Tip: Give every aggregate a name with AS (COUNT(*) AS orders). The backend code then reads row.orders instead of a strange column name like "COUNT(*)".
Careful: In SQLite, dividing two whole numbers gives a whole number: 7 / 2 is 3, not 3.5. AVG is safe because it always returns a decimal, but if you divide by hand, write SUM(amount) * 1.0 / COUNT(*).

Watch the lesson

Counting and totals: aggregate functions · 1/6

COUNT, SUM, AVG, MIN, MAX

Today we turn many rows into one answer: counts, totals and averages.

Prefer a video by a teacher? Find free videos on “sql count sum avg min max group by tutorial for beginners” on YouTube ↗

In real life: A kirana shop's daily report

At 10 pm, a kirana shop's billing app shows the owner one screen: 143 bills today, total 48,250 rupees, average bill 337.41 rupees, biggest bill 3,200 rupees, and a small table of sales per payment type (cash, UPI, card). Each number is one aggregate query, and the table is a GROUP BY payment_type. The database does the adding, so the app stays fast even after years of bills.

sql
SELECT COUNT(*) AS bills, SUM(amount) AS total,
       ROUND(AVG(amount), 2) AS average, MAX(amount) AS biggest
FROM bills
WHERE bill_date = '2026-10-19';
The whole daily summary in one query.

Practice (0/3 done)

Task 1 · Code · SQL

The orders table has id, customer, city, amount and rating (rating is NULL when the customer did not rate). In ONE query, return two numbers: how many orders there are in total, and how many orders have a rating.

Task 2 · Code · SQL

Return one row with four columns, in this order: the total of all amounts, the average amount rounded to 2 decimals, the smallest amount and the largest amount.

Task 3 · Code · SQL

Show one row per city with three columns: city, the number of orders, and the total amount. Sort by city from A to Z.

Quick quiz

Question 1

1.A table has 10 rows; the email column is NULL in 3 of them. What is COUNT(email)?

Question 2

2.Which function gives the highest fare?

Question 3

3.What does ROUND(825.625, 1) give?

Question 4

4.What does SELECT city, SUM(amount) FROM orders GROUP BY city return?

Question 5

5.In SQLite, what is 7 / 2?

Read more (free)

Pass the quiz (60% or more) or mark this topic as done when you are ready.

Your progress is saved on this device only.

Next: Joins: combining tables →

← Previous: INSERT, UPDATE and DELETE