SQL numeric functions

Numeric functions in SQL

Please do not copy without permission. © ExploreAI 2023.
Numeric functions in SQL

The importance of numeric functions in SQL

| Numeric functions are built-in functions that operate on numeric data types (such as integers,
decimals, and floating-points) and perform various mathematical and statistical operations on them.

_DATA MANIPULATION_ _DATA ANALYSIS_ _ACCURACY_

Allows for numerical Allows for data analysis Allows for accurate
data manipulation. by summarising numerical data
numerical datasets. calculations.

2
Numeric functions in SQL

Introduction to numeric functions in SQL

Aggregate numeric functions Scalar numeric functions

● MIN() ● ROUND()
● MAX() ● SQRT()
● AVG() ● LOG()
● SUM()
● COUNT()

Scalar numeric functions are not limited to the above. These will be discussed in this section.

3
Numeric functions in SQL

(General Household Survey (GHS) dataset

Province Y_2018 Y_2019 Y_2020 Y_2021 Y_2022 This dataset shows the average
monthly salaries of public
servants obtained from 2018 to
Eastern_cape
23612.117 24218.898 25785.763 27074.594 28049.845 2022 by province.
Free_state
25974.478 27897.917 29459.302 30932.342 48532.912 We will use it to illustrate
Gauteng different numeric functions.
31748.937 33112.344 34966.168 36715.740 38378.338

Kwazulu_natal
26188.484 27496.383 29141.264 30598.355 31914.132
Name: Salaries.ghs_db
Limpopo
30140.911 31726.672 33502.167 35178.147 36820.465

Mpumalanga
27321.994 28283.472 29867.734 31360.122 32963.496

North_west
25522.502 27621.283 29168.121 30626.714 32500.753

Northern_cape
27833.420 29288.380 30929.224 32475.897 33222.342

Western_cape
30164.275 31863.417 33647.873 35329.837 36919.034

4
Numeric functions in SQL

The MIN() function

| The MIN() function returns the smallest or lowest value of the selected column. Suppose we
want to calculate the lowest average monthly salary made in the year 2020.

Y_2020
SELECT
MIN(Y_2020) AS Lowest_salary_2020 25785.763
Query FROM 29459.302
Salaries.ghs_db;
34966.168

29141.264

33502.167

29867.734
Lowest_salary_2020
Output 29168.121
25789.763 30929.224

33647.873

5
Numeric functions in SQL

The MAX() function

| The MAX() function returns the largest or highest value of the selected column. Suppose we
want to calculate the highest average monthly salary made in the same year, 2020.

Y_2020
SELECT
MAX(Y_2020) AS Highest_salary_2020 25785.763
Query FROM
29459.302
Salaries.ghs_db;
34966.168

29141.264

33502.167

29867.734
Highest_salary_2020
Output 29168.121
34966.168
30929.224

33647.873
6
Numeric functions in SQL

The AVG() function

| The AVG() function returns the average value of the selected numeric column. Suppose we
want to calculate the average monthly salary made in the year 2022.

Y_2022
SELECT
AVG(Y_2022) AS Average_salary_2022 28049.845
Query FROM 48532.912
Salaries.ghs_db;
38378.338

31914.132

36820.465

32963.496
Average_salary_2022
Output 32500.753
35477.92419
33222.342

36919.034
7
Numeric functions in SQL

The SUM() function

| The SUM() function returns the total sum of a specified numeric column. Suppose we want to
find the sum of monthly salaries made in the year 2019.

Y_2019
SELECT
SUM(Y_2019) AS Total_salaries_2022 24218.898
Query FROM 27897.917
Salaries.ghs_db;
33112.344

27496.383

31726.672

28283.472
Total_salaries_2019
Output 27621.283
319301.3177
29288.380

31863.417
8
Numeric functions in SQL

The COUNT() function

| The COUNT() function returns the number of rows of a specified column. Suppose we want to
know how many provinces were used in this dataset.

Province
SELECT
eastern_cape
COUNT(Province) AS Number_of_provinces
Query FROM
free_state
gauteng
Salaries.ghs_db;
kwazulu_natal
limpopo
mpumalanga
north_west
Number_of_provinces
northern_cape
Output
9 western_cape

9
Numeric functions in SQL

The COUNT(DISTINCT column) function

|
The COUNT(DISTINCT column) function returns the distinct or unique number of rows of a
specified column. Suppose we want to know how many unique provinces were used in this
dataset.

SELECT
Query COUNT(DISTINCT Province) AS Number_of_provinces
FROM
Salaries.ghs_db;

The unique number of provinces is equal to

the number we got from the previous
Number_of_provinces
example. This is because there are no
Output
9 duplicate provinces in the Provinces
column.

10
Numeric functions in SQL

The ROUND() function

| The ROUND() function rounds a numerical value to a specified number of decimal places. The
syntax is as follows:

This clause specifies the numerical value to be

rounded.

This clause refers to the number of decimal

SELECT ROUND( numerical value , decimal places )
places to round the numerical value to.

11
Numeric functions in SQL

The ROUND() function

| Suppose we want to round the average monthly salary made in the year 2022 we calculated
before to the nearest cent.

Y_2022 Y_2022
SELECT
ROUND(Y_2022,2) AS Rounded_salaries 23612.12 23612.117
Query FROM 25974.48 25974.478
Salaries.ghs_db
31748.94 31748.937
LIMIT 3;
26188.48 26188.484

30140.91 30140.911

27321.99 27321.994
Rounded_salaries
Output 25522.50 25522.502
23612.12
27833.42 27833.420
25974.48
30164.28 30164.275
31748.94
12
Numeric functions in SQL

The SQRT() function

|
The SQRT() function returns the square root
of a numerical value. The syntax is as
follows:

SELECT SQRT( numerical value )

This clause specifies the numerical value to be

square rooted.
Numeric functions in SQL

The SQRT() function

| The SQRT() function returns the square root of a numerical value. The syntax is as follows:

Y_2021 Y_2021

SELECT 160.58… 27074.594

Query SQRT(Y_2021) AS Square_root_y_2021
171.64… 30932.342
FROM
186.99… 36715.740
Salaries.ghs_db
LIMIT 3; 170.71… 30598.355

183.04… 35178.147
Square_root_y_2021
172.82… 31360.122
Output
160.5794607… 170.79… 30626.714

171.6371221… 175.87… 32475.897

186.9924267… 183.43… 35329.837

14
Numeric functions in SQL

The LOG() function

|
The LOG(base, numerical) function returns
the logarithm of a numeric value with a
specified base. The syntax is as follows:

SELECT LOG( numerical_value, base );

This clause specifies the numerical This clause specifies the base of the
value to be logged. logarithm of the numerical value.
Numeric functions in SQL

The LOG() function

| Suppose we want to find the log of the average salaries and then round it to 2 decimal places.

Y_2021 Y_2021`
SELECT
LOG(Y_2021,10) AS Log_y_2021 4.564852289 27074.594
Query
FROM 4.485698078 30932.342
salaries.ghs_db
4.546272965 36715.740
LIMIT 3;
4.496377744 30598.355

4.486100396 35178.147
Log_y_2021
4.511561155 31360.122
Output
4.564852289… 4.548141639 30626.714

4.485698078… 4.564852289 32475.897

4.546272965… 4.485698078 35329.837

16