Function COUNT, SUM and AVG

MySQL have some predefined functions, which can be called using SQL statements. This are some basic mathematical functions such as COUNT, SUM, AVG, MAX and MIN functions.

Function COUNT

COUNT function returned the total number of data retrieved by a given SQL statement. The syntax to count the data is:

The example of the SQL statement is shown in Figure 1:

Figure 1: Using COUNT() to calculate the number of data in table "students".

In Figure 1, the first SQL statement shows the all the data in the table students, while the second statement shows output of COUNT(*) functions, which is the total number of data in the table.

Function SUM

SUM is a summation function, which is a mathematical formula to add all the value together. In SQL statement. The syntax to perform SUM function for a column is:

The SUM function can only perform for numerical column. Column that is text or varchar cannot perform such function. Figure 2 shows database administrator sum the intake years for the students table.

Figure 2: Summation for intake year in students table.

When user attempts to sum the value of a column that consists varchar or text, the SQL statements will returns value "0" and warnings for each of the data containing none numerical data. Figure 3 below shows the attempts of summing column that content none-numerical value.

Figure 3: Output for Summing none numerical data in MySQL / Maria DB.

Function AVG

AVG is the acronyms for "AVERAGE". The AVG function is used to calculate the average value for each of the value in the column. The operation add all numerical data from the column and divide by the number of data to output the average value for a particular column in the table. Figure 4 below shows the SQL statements to calculate the average intake year for the students in the table students.

Figure 4: Calculating average year for each student in table students.

Similar with summation function, the AVG function cannot process none-numerical data in the column. If there exist one or many none-numerical data in the column, the SQL statement will skip these column and only sum the column with numerical data. However, the AVG function still divide the summed value with the total row of data available.

In Figure 5 below, the student email for student with stdid "123456" is changed to value 12. After that the database administrator attempts to calculate the average value for the column "stdemail". The results show value "3", which is the value of "12" divided by 4 column of data.

Figure 5: Performing AVG function with none-numerical column.