SQL numeric functions

An introduction to SQL functions

Please do not copy without permission. © ALX 2024.
Introduction to SQL functions

What are SQL functions? 01.

|
SQL functions are built-in operations that can be used to perform various calculations,
manipulations, or transformations on data within an SQL database. Advantages of using
these functions include:

01.
01. Efficiency 04.
01. Extensive functionality
They take advantage of internal algorithms and data Database management systems provide a wide range
structures, which results in faster query execution times of built-in functions that allow for complex calculations,
and improved overall performance. string manipulations, and date and time operations.

02.
01. Reliability and compatibility 05.
01. Portability
They adhere to the SQL standards and are implemented SQL queries use standard functions; therefore, they can
consistently across different database platforms. be easily migrated or executed on different database
systems without significant modifications.

03.
01. Documentation and built-in support 06. Ease of use
They come with extensive documentation provided by They are readily available for use without any additional
the database management system vendors, making configuration. This saves you from reinventing the wheel
them easier for developers to understand and use by implementing common operations from scratch.
effectively.
2
Introduction to SQL functions

Syntax of SQL functions

| The output of a function in SQL is treated as a derived column and appears in the SELECT
clause of a query. The general syntax of an SQL function is:

This defines the specific SQL function we

want to use, such as SUM or COUNT.

These are the input arguments that the

SELECT function requires. Some functions do not
Column1, require any arguments while others
Function_name (Arguments) AS Named_result require one or more arguments.
FROM
Database_name.Table_name
It is good practice to name function
results descriptively with prefixes that
explain the function, e.g. total_sales for
SUM(sales).

3
Introduction to SQL functions

Function types

| Functions can be categorized by what input they take.

123 Numeric
These functions perform calculations on a set of values in a
abc column and return a numerical value.
SUM(), AVG(), COUNT(), MAX(), MIN(), POWER(),
SQRT(), ROUND()

String
These functions operate on string values and perform
operations such as concatenation, manipulation, and text
formatting.
CONCAT(), LENGTH(), SUBSTRING(), UPPER(), LOWER()

4
Introduction to SQL functions

Function types
Datetime
These are functions used to handle date and time values and
perform operations like formatting, extraction, and
manipulation.
123 CURRENT_DATE(), DATEADD(), FORMAT()

Misc abc Conditional flow

These functions allow for conditional logic in your SQL queries.
CASE(), IF(), IIF()

IF()

Miscellaneous
This category represents a variety of functions that do a
Many functions do not fit this classification! For
example, MIN() works with strings, numbers, and dates. variety of things such as converting data types and dealing
Focus on understanding how each function works, rather with NULL values.
than classifying them. CONVERT(), CAST(), NULLIF(), IFNULL()

5
Introduction to SQL functions

How functions behave

| SQL functions vary in behaviour. Aggregate functions summarise data at a column level.
Scalar functions manipulate data at the row level.

Patient_id Born FLOOR(Born) Scalar functions take a row and return a single value for
each row, e.g. UPPER(), LOWER(), CONVERT(), CAST(),
1 1969 1960
IF(), FORMAT() and arithmetic operators () +, -, *, /
3 1972 1970
Example:
7 1954 1950
FLOOR(Born) rounds the values in each row down to the
8 2004 2000 nearest integer. Each row has a new decade column with the
output of FLOOR().

AVG(Born) Aggregate functions take a set of rows as input and return a

single summary value, e.g. SUM(), AVG(), COUNT(),
1973.5 MAX(), MIN()

Window functions are a third way in which functions Example:

can behave which is a “hybrid” of aggregate and AVG(Born) calculates the average of the values in born,
scalar functions. and returns a single summary value of the column.
6
Introduction to SQL functions

Example data

To find the total amount of income expenditure in the Free State province in South Africa, we are going to
use the South African Household Income and Expenditure Survey dataset (SAHIES).

The table is named Income_expenditure_2020 in the Sahies database.

Expenditure_group Western Northern Free_state KwaZulu-Natal Northwest … Mpumalanga Limpopo

_cape _cape

Housing 16400 20000 24799 21200 21200 … 22799 23599

Recreation 1521 989 1255 1217 1179 … 1065 912

Transport 17974 15406 11983 13481 12625 … 12839 13481

7
Introduction to SQL functions

Example: Aggregate functions

| SUM() is an aggregate function that returns the total sum of a numeric column.

Free_state

24799
SELECT
SUM(Free_state) AS Total_free_state SUM 1255 = 38037
Query
` FROM
Sahies.Income_expenditure_2020; 11983

Total_free_state We see that our query returns a

Output single value, which is the sum of
38037 all rows of the Free_state
column.

8
Introduction to SQL functions

Example: Multiple aggregate functions

| We can aggregate multiple columns or the same column using a different function.

Suppose we want to calculate the total income spent for the Western and Free State provinces and the average spent for the
Free State province.

SELECT By not naming the

average spent in the
SUM(Western_cape) AS Total_spent_western_cape,
Free State, it is
SUM(Free_state) AS Total_spent_Free_state, assigned a default
Query AVG(Free_state) name that may not be
FROM as descriptive.
Sahies.Income_expenditure_2020;

Total_spent_western_cape Total_spent_Free_state AVG(Free_state)

Output
35895 38037 12679.0000

9
Introduction to SQL functions

Example: Multiple aggregate functions

| Since aggregate functions produce a single row, we cannot use them with the columns in
our main table.

SELECT
Free_state has 3 rows,
Free_state, and AVG(Free_state)
AVG(Free_state) has 1, so running this
Query FROM query results in an
Sahies.Income_expenditure_2020; error.

Output Error!

10
Introduction to SQL functions

Example: Scalar functions

| Suppose we want to calculate the difference in income expenditure between the Free
State and the Northern Cape province.

SELECT Free_state Northern_cape

Expenditure_group,
24799
- 20000
(Free_state
Query -
- Northern_cape) AS Diff_fs_and_nc 1255 989
FROM -
Sahies.Income_expenditure_2020; 11983 15406

Expenditure_group Diff_fs_and_nc
Note how this name is slightly harder to
Housing 4799 understand because we used abbreviations.

Output Recreation 266

We see that the calculation occurred on a row level because
Transport -3423 the results set includes a value for each row.

11
Introduction to SQL functions

Using functions together

|
We can use functions within functions, known as nesting. SQL evaluates the innermost function
first and then works its way outwards. The result of each inner function is used as the input for the
outer function.

SELECT ROUND() rounds a value

SUM(Western_cape) AS Total_spent_western_cape, to the specified amount
of decimals.
SUM(Northern_cape) AS Total_spent_northern_cape,
AVG(Free_state) is
Query ROUND(AVG(Free_state),0) AS Average_spent_free_state
calculated first, then
FROM rounded to 0 decimal
Sahies.Income_expenditure_2020; places.

Total_spent_western_cape Total_spent_northern_cape Average_spent_free_state

Output
35895 36395 12679

12
Introduction to SQL functions

SQL functions without input arguments

| Some SQL built-in functions do not require any arguments. They are used to perform
calculations or retrieve information.

CURRENT_DATE()
Returns the current date. Output:
CURRENT_DATE()
SELECT
Query: 2023-06-20
CURRENT_DATE();

Output:
RAND()
Generates a random number between 0 and 1. Limpopo Random_number

23599 0.81247…
SELECT
Query: Free_state, 912 0.01824…

RAND() AS Random_number FROM…; 13481 0.55863…

13