Functions in SQL - Aggregate Functions and Scalar Functions
Functions in SQL
There are two types of functions in SQL.
Aggregate Functions
These functions act on an entire set of data and not just on one data element.
Scalar Functions
These functions act only on single data values.
We will look at some specific examples of how to use these functions.
Sample Table for Aggregate Function Examples
We will use the following table in all aggregate function examples.
NUMBERTABLE
ID NO1 NAME
1 10 A
2 20 B
3 30 C
4 40 D
5 50 E
6 60 F
7 70 G
8 80 H
9 90 I
10 100 J
Aggregate Functions
SUM - The Addition Function
The SUM function returns the sum of all the values selected.
SELECT SUM(NO1) FROM NUMBERTABLE;
This will show the output:
NO1
550
To select only a few values, use the WHERE clause:
SELECT SUM(NO1) FROM NUMBERTABLE WHERE ID<6;
This will show the output:
NO1
150
You can also set the alias of the column name:
SELECT SUM(NO1) FROM NUMBERTABLE AS SUMX WHERE ID<6;
This will show the output:
SUMX
150
AVG - The Average Function
The average function returns the average of all the values selected.
SELECT AVG(NO1) AS AVERAGE FROM NUMBERTABLE;
This will show the output:
NO1
55
COUNT - The Count Function
The count function returns the number of all the values selected.
SELECT COUNT(NO1) AS COUNTX FROM NUMBERTABLE;
This will show the output:
COUNTX
10
MAX - The Maximum Function
The MAX function returns the maximum of all the values selected.
SELECT MAX(NO1) AS XMAX FROM NUMBERTABLE;
This will show the output:
XMAX
100
MIN - The Minimum Function
The MIN function returns the minimum of all the values selected.
SELECT MIN(NO1) AS XMIN FROM NUMBERTABLE;
This will show the output:
XMIN
10
FIRST
The FIRST function returns the first of the values selected.
SELECT FIRST(NO1) AS XFIRST FROM NUMBERTABLE;
This will show the output:
XFIRST
10
LAST
The LAST function returns the last of the values selected.
SELECT LAST(NO1) AS XLAST FROM NUMBERTABLE;
This will show the output:
XLAST
100
Besides these aggregate functions, we also have scalar functions which operate on only one data value.
Sample Table for Scalar Function Examples
We will use this table for scalar function examples:
NAME: ID FIRST LAST NO
1 PAT aaa 10.2
2 MOO yo 20.4
3 RAM yoo 30
4 JIM choo 40
Scalar Functions
UCASE Function
This function converts the text to uppercase and returns it.
SELECT LAST FROM NAME WHERE ID=1;
The output for this command yields:
LAST
aaa
SELECT UCASE(LAST) FROM NAME WHERE ID=1;
Our output is:
LAST
AAA
LCASE Function
This function converts the text to uppercase and returns it.
SELECT LCASE(FIRST) FROM NAME WHERE ID=1;
Output:
FIRST
pat
LEN Function
This function returns the length of the data value.
SELECT LEN(FIRST) FROM NAME WHERE ID=1;
Output:
3
ROUND Function
This rounds the value of the data element and returns it.
The basic syntax is,
SELECT ROUND(DATA, NO_OF_DECIMALS) FROM TABLENAME;
For example,
SELECT ROUND(NO, 0) FROM NAME;
Output:
NO
10
20
30
40
It rounds the values 10.2 to 10, 20.4 to 20, and so forth, and then displays them.
This post is part of the series: Introduction to SQL
This series is an introduction to SQL. It includes details on tables, DDL and DML queries, joins in SQL, and more.
