SQL guide

SQL Functions: Aggregate, String, Date and Conditional

SQL functions transform and summarize data. This guide covers the ones you will use most, with examples on a simple orders table.

The example table

The examples use a table called orders with the columns id, customer, city, amount, status and order_date.

Aggregate functions

Aggregates collapse many rows into one value. Ignore NULLs, except COUNT(*).

SELECT COUNT(*)      AS orders,
       SUM(amount)   AS revenue,
       AVG(amount)   AS average_order,
       MIN(amount)   AS smallest,
       MAX(amount)   AS largest
FROM orders;

Combine them with GROUP BY to get one result per group, for example revenue per city.

String functions

Names vary between databases (for example LEN in SQL Server and LENGTH in MySQL and PostgreSQL).

SELECT UPPER(customer)            AS name_upper,
       LOWER(city)                AS city_lower,
       TRIM(customer)             AS clean_name,
       SUBSTRING(customer, 1, 3)  AS first_three,
       REPLACE(city, 'Bangalore', 'Bengaluru') AS fixed_city,
       CONCAT(customer, ' - ', city) AS label
FROM orders;

Numeric functions

SELECT ROUND(amount * 0.18, 2) AS gst,
       CEIL(amount / 100.0)    AS hundreds_up,
       FLOOR(amount / 100.0)   AS hundreds_down
FROM orders;

Date functions

Date functions differ the most between databases, so check your documentation. Common ideas are getting today's date (CURRENT_DATE), extracting a part (EXTRACT(MONTH FROM order_date) or MONTH(order_date)) and finding the difference between dates (DATEDIFF in MySQL and SQL Server).

Conditional functions

CASE returns different values by condition; COALESCE returns the first non-NULL value; NULLIF returns NULL when two values match, which prevents divide-by-zero.

SELECT id,
       CASE WHEN amount >= 10000 THEN 'Large'
            WHEN amount >= 1000  THEN 'Medium'
            ELSE 'Small' END      AS size,
       COALESCE(city, 'Unknown') AS city
FROM orders;

Practice filtering first with SELECT and WHERE, then combine tables with JOINs.

Frequently asked questions

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts all rows. COUNT(column) counts rows where that column is not NULL.

Why does my function name give an error?

Function names differ between MySQL, PostgreSQL, SQL Server and Oracle. Look up the one for your database.

Can I use functions in WHERE?

Yes, for example WHERE UPPER(city) = 'PUNE', though wrapping a column in a function can stop an index from being used.

← Back to SQL