# Functions in SQL - Aggregate Functions and Scalar Functions

Page content

## 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

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.