SQL Aggregate and Scalar Functions

Page 1

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

AVG() - Returns the average value

SELECT AVG(column_name) FROM table_name;

COUNT() - Returns the number of rows

SELECT COUNT(column_name) FROM table_name;

SELECT COUNT(DISTINCT column_name) FROM table_name;

FIRST() - Returns the first value

MYSQL: SELECT column_name FROM table_name

ORDER BY column_name ASC LIMIT 1;

ORACLE: SELECT column_name FROM table_name

WHERE ROWNUM <=1 ORDER BY column_name ASC;

SQLSERVER: SELECT TOP 1 column_name FROM table_name

ORDER BY column_name ASC;

LAST() - Returns the last value

mesma sintaxe que FIRST(), mas usando DESC

MAX() - Returns the largest value

SELECT MAX(column_name) FROM table_name;

MIN() - Returns the smallest value

SELECT MIN(column_name) FROM table_name;

SUM() - Returns the sum

SELECT SUM(column_name) FROM table_name;

GROUP BY - Used with aggregate functions

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name;

HAVING - Filter used with aggregate functions

SELECT column_name, aggregate_function(column_name)

FROM table_name

WHERE column_name operator value

GROUP BY column_name

HAVING aggregate_function(column_name) operator value;

SQL Scalar functions SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

UCASE() - Converts a field to upper case

LCASE() - Converts a field to lower case

MID() - Extract characters from a text field

LEN() - Returns the length of a text field

ROUND() - Rounds a numeric field to the number of decimals specified

NOW() - Returns the current system date and time

FORMAT() - Formats how a field is to be displayed