A Dive into SQL Functions

#sql #learning #database #postgres

Introduction

A month into learning SQL, I hit a point where every query i wrote was SELECT * FROM table WHERE. It worked perfectly but it had become obvious to retrieve data from a table and not actually work with it.
Getting into functions is where the real work is, aggregating data, transforming values and handling dates.
This article walks through what are SQL functions, common functions, practical examples and when to use them.

What are SQL Functions?

SQL functions are predefined or user-defined operations in SQL that take inputs, perform a specific operation, and return a single value. They are used to simplify queries, calculations, and data manipulations.

Common Functions

1.Aggregate functions
This takes in many rows and collapse them into a single value. COUNT,SUM, AVG,MIN,MAX are the examples. If used withoutGROUP BY, you get one row back for the whole table but when you use GROUP BY, you get one row per group.

i)- SUM(column) – total of column values

----- Find the total transaction amount by Transaction Type.  
select "Transaction_Type",sum("Amount") as transaction_Type_Amount
from money.moniepoint_transactions
group by "Transaction_Type";

ii)- AVG(column) – average

---- get the average
select avg("Amount")
from money.moniepoint_transactions;

iii)- COUNT(column) – number of rows

MIN(column) / MAX(column) – minimum or maximum value

Example

---Count how many orders each customer_id has placed (GROUP BY customer_id).
select  customer_id, count(*) as order_count
from supermarket.order_supermarket 
group by customer_id;

This returns the order count each customer placed.

2.** Scalar functions**
Scalar functions operate on a single value and return one result per row, without changing the row count. String functions (UPPER, CONCAT, SUBSTRING), numeric functions (ROUND, ABS), and most date functions (YEAR, DATEDIFF) fall into this category.

Examples and Categories
a) String functions — operate on text values.

LEN() – returns length of a string
UPPER() / LOWER() – converts to upper/lower case
SUBSTRING() – extracts part of a string

b) Numeric functions — operate on numbers.

ABS()– absolute value
ROUND() – rounds a number

c) Date/Time functions — operate on date or time.

GETDATE() – current date and time
DATEADD(day, 5, GETDATE()) – adds 5 days to a date
DATEDIFF(day, startDate, endDate) – difference between dates

When to Use Them

a)Aggregate fuctions
Used when the question is about a group, like how many, what's the average, what's the total. It answers the questions that has COUNT,SUM, AVG, MIN or MAX.

b) Scalar functions
Used when there is need to reshape or clean a value without changing how many rows come back. This involves formatting names, rounding numbers and building labels.

Summary

SQL functions help you move beyond simply retrieving data.
Aggregate functions summarize data using COUNT, SUM, AVG, MIN, and MAX.
Scalar functions transform individual values without changing the number of rows.
String functions help manipulate text.
Numeric functions help perform calculations and rounding.
Date/time functions make it easier to work with dates and time differences.
Using functions makes SQL queries more powerful, efficient, and meaningful.
Mastering SQL functions is an important step toward real-world data analysis.

DEV Community