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).
■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.

SUMSum of arguments: if NULL, not included in the tally
MAXFind the maximum value of the argument.
MINFind the minimum value of the argument.
AVGFind the average value of the arguments; if NULL, it is not included in the tally.
COUNTIf it is NULL, it is not counted.Can be described as COUNT(*).


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
goodsNodate quantity
NP20020031010 3
FP23320031010 2
NP20020031011 1
FP23320031011 2

ASELECT AVG(quantity) FROM Delivery Record WHERE goodsNo ='NP200'
BSELECT COUNT(*) FROM Delivery Record
CSELECT MAX(quantity) FROM Delivery Record
DSELECT SUM(quantity) FROM Delivery Record WHERE date = '20031011'



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).