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

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.

Practice (1/2) Questions

Find the sum, maximum, minimum, mean, and total number of SALs (salaries) from the EMP Table.

Practice (1/2) Answers

SELECT	SUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL),COUNT(SAL)
FROM	EMP

The average can also be obtained arithmetically as SUM(SAL)/COUNT(SAL).

Practice (2/2) Questions

Find the maximum and minimum value of the SAL (salary) from the EMP Table for the JOB (job) 'SALESMAN'.

Practice (2/2) Answers

SELECT	MAX(SAL),MIN(SAL)
FROM	EMP
WHERE	JOB = 'SALESMAN'

Only records matching the WHERE condition will be processed by the set function.

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

Beginner System Administrator AM Fall 2003 Q26

Question
Answer and Explanation

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'

Answer

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