SQL: Mastering Functions — Universal and Aggregate Unveiled

Conquer SQL: From Zero to Resume — Part 2 (CAST, COALESCE, ROUND, LENGTH, SUBSTR, LOWER/UPPER, STRFTIME, AVG, COUNT, MAX, MIN, SUM)

What functions we will learn in the below:

1. Universal functions: CAST, COALESCE, ROUND, LENGTH, SUBSTR, LOWER/UPPER, STRFTIME
2. Aggregate functions: AVG, COUNT, MAX, MIN, SUM

Understanding SQL Basics, DBMS, Concepts, and Derived Columns

Contents

• What are Functions?
• What are Universal functions?
• What are Aggregate functions?

Photo by Claudio Schwarz on Unsplash

What are Functions

A function is a predefined operation that maps inputs to outputs, enabling tasks such as numerical calculations and text manipulation to be efficiently performed.

The operation of a function consists of five components:

1. Function name
2. Input
3. Parameters
4. Processing logic
5. Output

Functions can be broadly classified into two main categories based on their purpose. The key difference between universal functions and aggregate functions lies in the dimension they operate on:

• Universal functions work horizontally.
• Aggregate functions work vertically.

Characteristics:

Universal functions: Each observation produces a single output.

Aggregate functions: An entire column produces a single output.

👆Back to the contents

What are Universal functions?

What functions we will learn in the below:
1. CAST
2. COALESCE
3. ROUND
4. LENGTH
5. SUBSTR
6. LOWER/UPPER
7. STRFTIME

Universal functions can also be categorized based on the applied data types:

1. Data type conversion
2. Numerical calculations
3. Text manipulation
4. Date/time handling

Data type conversion

The CAST function is used to convert the data type of a query result into a specified data type:

CAST(data AS data_type)

The COALESCE function is used to replace NULL values (or missing values) with a specified constant.

COALESCE(NULL, replacement)

Numerical calculations

The ROUND function is used to adjust the number of decimal places in the query result.

ROUND(REAL, n_digits)

Text manipulation

The LENGTH function counts the number of characters in a text, including spaces and punctuation marks.

LENGTH(TEXT)

The SUBSTR function extracts a specified substring from a text based on its position.

SUBSTR(TEXT, start, stop)

The LOWER and UPPER functions allow you to change the case of English text.

LOWER(TEXT)
UPPER(TEXT)

Date/time handling

Standard Date, Time, and DateTime Formats

• ISO8601 is the standard format for date and time.
• Dates YYYY-MM-DD
• Time HH:MM:SS
• Date & time YYYY-MM-DD HH:MM:SS

When date and time data are stored in the ISO8601 format in a database, it ensures high convenience in usage.

Get Mini Cactus’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

The STRFTIME function allows you to adjust the display format for dates, times, or date-time values.

STRFTIME(format, DATE/TIME/DATETIME)
-- format: '%d', '%j', '%m', '%w', '%W', '%Y'

Common date and date-time format parameters include:

• %d: Two-digit day (01~31)
• %j: Day of the year (001~366)
• %m: Two-digit month (01~12)
• %w: Day of the week (0~6)
• %W: Week number of the year (00~53)
• %Y: Four-digit year (0000~9999)

👆Back to the contents

What are Aggregate functions?

Common Aggregate Functions:

• AVG(column_name): Calculates the average value of a variable.
• COUNT(column_name): Counts the number of non-NULL values in a column.
• COUNT(*): Counts the total number of observations in a table.
• MAX(column_name): Returns the maximum value of a variable.
• MIN(column_name): Returns the minimum value of a variable.
• SUM(column_name): Calculates the sum of a variable.

👆Back to the contents

SQL: Reserved Keywords for Sort & Filter Query Results