Aggregate Functions: COUNT, SUM, AVG, MAX and MIN

Hello students, welcome to Kwickprep. Five students took a test, so why can the average marks come out as seventy nine and not sixty three point two? The answer hides in one empty cell. Today we will learn count, sum, average, max and min, and how empty values change them.

All our examples use one table called student. It has five columns: roll, name, section, marks and city. Kabir was absent for the test, so his marks are NULL. NULL means no value at all. It is not zero, and it is not a blank space.

Aggregate means to collect many things into one. An aggregate function reads a whole column and gives back just one value. Here, the marks column has five cells. The NULL cell is skipped. The four numbers are added together. The whole column collapses into one answer, three hundred sixteen.

Do not mix up the two kinds of functions. A single row function, like ucase, gives one answer for each row. An aggregate function, like sum, gives one answer for a whole set of rows. Some books call them group functions or multiple row functions.

There are five aggregate functions to learn. Count tells how many values there are. Sum adds all the values. Avg gives the average, which is the sum divided by the count. Max gives the largest value. Min gives the smallest value.

Count comes in two forms, and exams test the difference. Count star counts every row, so the answer is five. Count of marks counts only the rows where marks is not NULL. Kabir has no marks, so the answer is four.

We can also count only the different values. Count distinct city counts Surat, Pune and Delhi once each. So the answer is three, not five.

Sum of marks adds eighty eight, seventy two, sixty five and ninety one, which is three hundred sixteen. Avg divides that sum by four, not five, because the NULL row is skipped. So the average is seventy nine. MySQL shows it with four decimal places. Sum and avg work only on numbers.

Max of marks gives the highest score, ninety one. Min of marks gives the lowest, sixty five, and the NULL is ignored, so it is not zero. Max and min also work on text and dates. Max of name gives Riya, because Riya comes last in dictionary order.

Now the big idea of this lesson. Count star counts every row, whether it has NULL or not. All the other aggregates simply skip NULL values. So avg divides by the values present, not by the number of rows. NULL is never treated as zero.

Here is the hook from the start. Pause and predict. If Kabir's marks were zero instead of NULL, what is the average? Now there are five values with a total of three hundred sixteen. Three hundred sixteen divided by five is sixty three point two. Zero is a real value, but NULL is no value.

Where runs first and picks the rows. Then the aggregate works only on those rows. Section A has Riya, Neha and Meera. So count star is three, and the highest mark among them is ninety one.

Here are mistakes to avoid. An aggregate function cannot be written inside where. So where avg of marks greater than seventy gives an error, and the next lesson shows the fix with having. To count rows, use count star, because count of a column skips NULL values. Give the result a clear heading with an alias, like sum of marks as Total.

Let us revise what we learned today. An aggregate function collapses many rows into one value. Count star counts all rows, but count of a column skips NULL. Sum, avg, max and min give the total, average, largest and smallest. Every aggregate except count star ignores NULL values. So avg divides only by the values present. Practise with a table that has a NULL, and predict every answer first.