SQL Functions

SQL has many built-in functions for performing calculations on data.

Sql has two type functions

  • Aggregate Functions
  • Scalar Functions

Aggregate Functions

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

list of aggregate functions:

AVG(expression)
Computes the average value of a column by the expression
Syntax
SELECT AVG(column_name) FROM table_name

COUNT(expression)

Counts the rows defined by the expression
Syntax
SELECT COUNT(column_name) FROM table_name

COUNT(*)

Counts all rows in the specified table or view
Syntax
SELECT count(*) FROM table_name

MIN(expression)

Finds the minimum value in a column by the expression
Syntax
SELECT MIN(column_name) FROM table_name

MAX(expression)

Finds the maximum value in a column by the expression
Syntax
SELECT MAX(column_name) FROM table_name

SUM(expression)

Computes the sum of column values by the expression
Syntax
SELECT SUM(column_name) FROM table_name

 

Scalar Functions

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

UCASE()
Converts a field to upper case

Syntax
SELECT UCASE(column_name) FROM table_name

LCASE()
Converts a field to lower case

Syntax
SELECT LCASE(column_name) FROM table_name

MID()
Extract characters from a text field

Syntax
SELECT MID(column_name,start[,length]) FROM table_name

LEN()
Returns the length of a text field

Syntax
SELECT LEN(column_name) FROM table_name

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

Syntax
SELECT ROUND(column_name,decimals) FROM table_name

NOW()
Returns the current system date and time

Syntax
SELECT NOW() FROM table_name

FORMAT()
Formats how a field is to be displayed

Syntax
SELECT FORMAT(column_name,format) FROM table_name

Bookmark This Page

Link Partners