Aggregate, window, string, date and analytic functions, with worked examples.
Window functions perform calculations across a set of rows that are related to the current row, but unlike GROUP BY, they don't collapse rows. Each row retains its identity while still accessing aggregate information.
SELECT
employee_name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) as dept_avg_salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM employees;
SELECT
department,
AVG(salary) as dept_avg_salary
FROM employees
GROUP BY department;
Aggregate functions perform calculations on multiple rows and return a single value. They are typically used with GROUP BY clauses.
SELECT
COUNT(*) as total_employees,
SUM(salary) as total_salary,
AVG(salary) as average_salary,
MAX(salary) as highest_salary,
MIN(salary) as lowest_salary,
STDDEV(salary) as salary_stddev,
VARIANCE(salary) as salary_variance
FROM employees;
SELECT
department,
COUNT(*) as emp_count,
AVG(salary) as avg_salary,
SUM(salary) as dept_budget
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
These are window functions used for ranking rows. They differ in how they handle ties and gaps in ranking.
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
RANK() OVER (ORDER BY salary DESC) as rank,
DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees
WHERE department = 'Sales';
Name | Salary | ROW_NUMBER | RANK | DENSE_RANK -----------|--------|------------|------|----------- Alice | 100000 | 1 | 1 | 1 Bob | 90000 | 2 | 2 | 2 Charlie | 90000 | 3 | 2 | 2 David | 80000 | 4 | 4 | 3
String functions manipulate text data. Different databases have similar functions but sometimes with different names.
SELECT
name,
-- Length/Position functions
LENGTH(name) as name_length,
CHAR_LENGTH(name) as char_count, -- Same as LENGTH for single-byte
POSITION(' ' IN name) as space_position,
INSTR(name, ' ') as space_pos_mysql,
-- Case conversion
UPPER(name) as upper_name,
LOWER(name) as lower_name,
-- Substring/extraction
SUBSTRING(name, 1, 5) as first_5_chars,
LEFT(name, 3) as first_3_chars,
RIGHT(name, 3) as last_3_chars,
-- Trimming
TRIM(name) as trimmed_name,
LTRIM(name) as left_trimmed,
RTRIM(name) as right_trimmed,
-- Replacement
REPLACE(name, ' ', '_') as name_with_underscores
FROM employees;
-- Concatenation
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM employees;
-- MySQL: CONCAT(), PostgreSQL: || operator, SQL Server: + operator
-- Pattern matching
SELECT name FROM employees WHERE name LIKE 'J%'; -- Starts with J
SELECT name FROM employees WHERE name LIKE '%son'; -- Ends with son
SELECT name FROM employees WHERE name LIKE '%oh%'; -- Contains oh
-- Padding
SELECT LPAD(salary::text, 10, '0') as padded_salary FROM employees; -- Left pad
SELECT RPAD(name, 20, '.') as padded_name FROM employees; -- Right pad
-- Soundex (phonetic matching)
SELECT name FROM employees WHERE SOUNDEX(name) = SOUNDEX('Jon');
Date and time functions allow manipulation and extraction of temporal data. Different databases have similar but sometimes different syntax.
-- Current date/time
SELECT
CURRENT_DATE as today_date, -- 2025-12-02
CURRENT_TIMESTAMP as now, -- 2025-12-02 14:30:45
NOW() as mysql_now, -- MySQL specific
GETDATE() as sqlserver_now, -- SQL Server specific
SYSDATE as oracle_now; -- Oracle specific
-- Date extraction
SELECT
EXTRACT(YEAR FROM order_date) as order_year,
EXTRACT(MONTH FROM order_date) as order_month,
EXTRACT(DAY FROM order_date) as order_day,
DATE_PART('dow', order_date) as day_of_week, -- 0=Sunday
DAY(order_date) as day_mysql, -- MySQL
DATEPART(month, order_date) as month_sqlserver; -- SQL Server
-- Date arithmetic
SELECT
order_date,
order_date + INTERVAL '7 days' as due_date, -- PostgreSQL/MySQL
DATEADD(day, 7, order_date) as due_date_sqlserver, -- SQL Server
order_date - INTERVAL '1 month' as last_month,
DATEDIFF('day', order_date, CURRENT_DATE) as days_ago, -- MySQL
DATE_DIFF(CURRENT_DATE, order_date, DAY) as days_diff; -- BigQuery
-- Date formatting
SELECT
TO_CHAR(order_date, 'YYYY-MM-DD') as iso_format,
TO_CHAR(order_date, 'Month DD, YYYY') as long_format,
DATE_FORMAT(order_date, '%W, %M %e, %Y') as mysql_format,
FORMAT(order_date, 'dd/MM/yyyy') as sqlserver_format;
-- Date validation
SELECT
order_date,
order_date::date as date_only, -- Cast to date
ISDATE(order_date_str) as is_valid_date; -- SQL Server
-- Find orders from last 30 days
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';
-- Group by month-year
SELECT
TO_CHAR(order_date, 'YYYY-MM') as month,
COUNT(*) as order_count,
SUM(amount) as total_amount
FROM orders
GROUP BY TO_CHAR(order_date, 'YYYY-MM')
ORDER BY month;
-- Calculate age
SELECT
name,
birth_date,
EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) as age
FROM employees;
PIVOT rotates rows to columns (transposing), while UNPIVOT rotates columns to rows. Useful for reporting and data transformation.
-- Sample sales data
SELECT * FROM monthly_sales;
-- year | month | amount
-- 2024 | Jan | 1000
-- 2024 | Feb | 1500
-- 2024 | Mar | 1200
-- 2025 | Jan | 1100
-- 2025 | Feb | 1600
-- Pivot to show months as columns
SELECT *
FROM monthly_sales
PIVOT (
SUM(amount)
FOR month IN ([Jan], [Feb], [Mar], [Apr])
) AS pivoted_sales;
-- Result:
-- year | Jan | Feb | Mar | Apr
-- 2024 | 1000 | 1500 | 1200 | NULL
-- 2025 | 1100 | 1600 | NULL | NULL
-- Using CASE statements
SELECT
year,
SUM(CASE WHEN month = 'Jan' THEN amount END) as Jan,
SUM(CASE WHEN month = 'Feb' THEN amount END) as Feb,
SUM(CASE WHEN month = 'Mar' THEN amount END) as Mar,
SUM(CASE WHEN month = 'Apr' THEN amount END) as Apr
FROM monthly_sales
GROUP BY year;
-- Sample pivoted data
CREATE TABLE sales_pivot (
year INT,
Q1 DECIMAL,
Q2 DECIMAL,
Q3 DECIMAL,
Q4 DECIMAL
);
-- Unpivot quarterly data
SELECT year, quarter, amount
FROM sales_pivot
UNPIVOT (
amount FOR quarter IN (Q1, Q2, Q3, Q4)
) AS unpivoted_sales;
-- Result:
-- year | quarter | amount
-- 2024 | Q1 | 1000
-- 2024 | Q2 | 1500
-- 2024 | Q3 | 1200
-- 2024 | Q4 | 1800
Analytic functions compute aggregate values while retaining individual row details, unlike aggregate functions that collapse multiple rows into one.
-- Returns single row with totals
SELECT
department,
COUNT(*) as emp_count,
AVG(salary) as avg_salary,
SUM(salary) as total_salary
FROM employees
GROUP BY department;
-- Result (collapsed):
-- department | emp_count | avg_salary | total_salary
-- Sales | 25 | 65000 | 1625000
-- Engineering| 50 | 85000 | 4250000
-- Returns all rows with additional analytic columns
SELECT
name,
department,
salary,
-- Analytic functions
AVG(salary) OVER (PARTITION BY department) as dept_avg_salary,
SUM(salary) OVER (PARTITION BY department) as dept_total_salary,
salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary) as percentile,
LAG(salary, 1) OVER (PARTITION BY department ORDER BY hire_date) as prev_salary
FROM employees;
-- Result (individual rows preserved):
-- name | department | salary | dept_avg | dept_total | diff | rank | ...
-- John | Sales | 70000 | 65000 | 1625000 | 5000 | 1 | ...
-- Jane | Sales | 60000 | 65000 | 1625000 | -5000| 2 | ...
SQL provides various mathematical and statistical functions for numeric data analysis and calculations.
SELECT
-- Basic arithmetic
10 + 5 as addition,
10 - 5 as subtraction,
10 * 5 as multiplication,
10 / 5 as division,
10 % 3 as modulus,
-- Power and roots
POWER(2, 3) as power, -- 2^3 = 8
SQRT(16) as square_root, -- 4
EXP(1) as exponential, -- e^1 ≈ 2.718
LOG(100, 10) as logarithm, -- log₁₀100 = 2
LN(EXP(1)) as natural_log, -- ln(e) = 1
-- Rounding
ROUND(123.4567, 2) as rounded, -- 123.46
CEILING(123.45) as ceiling, -- 124 (smallest integer ≥ value)
FLOOR(123.45) as floor, -- 123 (largest integer ≤ value)
TRUNC(123.456, 1) as truncate, -- 123.4 (PostgreSQL)
-- Sign and absolute
ABS(-123) as absolute, -- 123
SIGN(-123) as sign, -- -1
SIGN(0) as sign_zero, -- 0
SIGN(123) as sign_positive; -- 1
SELECT
-- Angles in radians
PI() as pi, -- 3.14159
RADIANS(180) as radians, -- π
DEGREES(PI()) as degrees, -- 180
-- Basic trig
SIN(PI()/2) as sine, -- 1
COS(PI()) as cosine, -- -1
TAN(PI()/4) as tangent, -- 1
-- Inverse trig
ASIN(1) as arcsine, -- π/2
ACOS(0) as arccosine, -- π/2
ATAN(1) as arctangent, -- π/4
-- Hyperbolic
SINH(1) as hyperbolic_sine,
COSH(0) as hyperbolic_cosine,
TANH(0.5) as hyperbolic_tangent;
-- Basic statistics
SELECT
AVG(salary) as mean_salary,
MEDIAN(salary) as median_salary, -- Some databases
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) as median,
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) as q1,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) as q3,
STDDEV(salary) as std_deviation,
VARIANCE(salary) as variance,
CORR(salary, commission) as correlation,
COVAR_POP(salary, commission) as covariance
FROM employees;
-- Window statistical functions
SELECT
name,
salary,
AVG(salary) OVER () as overall_mean,
salary - AVG(salary) OVER () as diff_from_mean,
(salary - AVG(salary) OVER ()) / STDDEV(salary) OVER () as z_score
FROM employees;