SQL Functions

#backend #database #sql

What are SQL functions?

A SQL function is a built-in operation that takes some input - a column, a value, or a set of rows - and returns a computed result. They fall into two broad categories:

Aggregate functions - operate across multiple rows and collapse them into a single value: COUNT, SUM, AVG, MIN, MAX.
Scalar functions - operate on a single value at a time and return one result per row: string functions (UPPER, LOWER, CONCAT, LENGTH), numeric functions (ROUND, ABS), and date functions (NOW(), DATE_PART, AGE()).

Common functions

Function	Category	What it does
`COUNT()`	Aggregate	Counts rows (or non-null values in a column)
`SUM()`	Aggregate	Adds up numeric values
`AVG()`	Aggregate	Calculates the mean
`MIN()` / `MAX()`	Aggregate	Smallest / largest value
`UPPER()` / `LOWER()`	String	Changes text case
`CONCAT()`	String	Joins strings together
`ROUND()`	Numeric	Rounds a decimal to a given precision
`NOW()`	Date	Returns the current timestamp

Examples - from the Sunrise Supermarket project

Counting orders per customer - an aggregate function paired with GROUP BY:

SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id;

Adding up total quantity sold per product:

SELECT products.product_name, SUM(order_items.quantity) AS total_quantity
FROM products
INNER JOIN order_items ON products.product_id = order_items.product_id
GROUP BY products.product_name;

Scalar functions applied on top of the same schema — formatting a product name in uppercase for a report, and calculating price including a hypothetical 16% VAT, rounded to two decimal places:

SELECT
    UPPER(product_name) AS product_display,
    ROUND(unit_price * 1.16, 2) AS price_with_vat
FROM products;

When to use them

Aggregate functions are the right tool whenever the question is about a group of rows rather than any single row - "how many," "what's the total," "what's the average." The moment GROUP BY enters a query, there's almost always an aggregate function sitting next to it.

Scalar functions come up constantly for formatting and light computation - cleaning up text for display, converting units, rounding currency values so they don't show absurd precision like 179.999999.

What I understood from this

The thing that clarified aggregate functions for me was realizing they only make sense in the context of grouping - COUNT(order_id) on its own, with no GROUP BY, just counts all the rows in the result set as one number. It's the same function, but GROUP BY customer_id is what turns it from "one total" into "one total per customer." Scalar functions, by contrast, never need that - they just transform each row independently, which is why you'll often see them mixed freely into a SELECT list without touching the rest of the query's logic at all.

DEV Community