SQL Strategy - A site to master SQL while executing it on the Web
Home >> SQL Strategy - Functions (SUM, MAX, MIN, AVG, COUNT)

## Functions (SUM, MAX, MIN, AVG, COUNT)

Students learn set functions to find the sum, maximum, minimum, mean, and total number of records extracted.

### 1. how to use set functions

--Find the maximum and minimum values of SAL (salary).
 SELECT MAX(SAL),MIN(SAL) FROM EMP
##### ■How to use set functions

The aggregate function aggregates the values of the columns specified in the argument and returns the result; if the WHERE condition is specified, it returns the aggregate result for the extracted records.

##### ■Types and functions of set functions

SQL provides the following functions. SUM and AVG cannot take non-numeric arguments.

 SUM Sum of arguments: if NULL, not included in the tally MAX Find the maximum value of the argument. MIN Find the minimum value of the argument. AVG Find the average value of the arguments; if NULL, it is not included in the tally. COUNT If it is NULL, it is not counted.Can be described as COUNT(*).

##### ■Practice

Now for the Practice, I will give you two questions.

### 2. past question of Functions (SUM, MAX, MIN, AVG, COUNT)

Beginner System Administrator AM Fall 2003 Q26
Which SQL statement for the "Delivery Record" Table yields the largest value?

Delivery Record
 goodsNo date quantity NP200 20031010 3 FP233 20031010 2 NP200 20031011 1 FP233 20031011 2

 A SELECT AVG(quantity) FROM Delivery Record WHERE goodsNo ='NP200' B SELECT COUNT(*) FROM Delivery Record C SELECT MAX(quantity) FROM Delivery Record D SELECT SUM(quantity) FROM Delivery Record WHERE date = '20031011'

B

Explanation

If you verify the execution results in turn, you will get to Answer.

A is the average value of the QUANTITY of WHERE goodsNo ='NP200', so 2
B is the number of records, so 4
C is for the maximum value of QUANTITY, so 3
D is the total value of QUANTITY in WHERE date = '20031011', so 3

Therefore, the largest value, B, is the Answer.

This completes Functions (SUM, MAX, MIN, AVG, COUNT).