Pin Me

Aggregate and Scalar Functions in SQL

written by: •edited by: Michele McDonough•updated: 4/28/2010

This tutorial will explain everything about the basic functions in SQL. It covers everything from the syntax to the working of scalar and aggregate functions.

  • slide 1 of 5

    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.

  • slide 2 of 5

    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

  • slide 3 of 5

    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.

  • slide 4 of 5

    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

  • slide 5 of 5

    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.

Introduction to SQL

This series is an introduction to SQL. It includes details on tables, DDL and DML queries, joins in SQL, and more.
  1. Short Introduction to SQL
  2. DML Queries in SQL
  3. Aggregate and Scalar Functions in SQL
  4. Using Joins in SQL