SQL Aggregate Functions

SQL Aggregate Functions

The functions that return only a single value are called Aggregate Functions. These functions perform operations on one or more than one values stored within the column.

Commonly used aggregate functions include:

  • AVG()                   –             Used for returning an average value
  • COUNT()              –             Used for returning the count of value(s)
  • TOP()                    –             Used for returning the top (first) value
  • MAX()                   –             Used for returning the maximum value
  • MIN()                    –             Used for returning the minimum value
  • SUM()                   –             Used for returning the sum of values falling within the range

SQL Scalar functions

Scalar functions are the functions that, on the basis of the input value, return a single value.

Commonly used scalar functions include:

  • UCASE()               –             Used for converting a value to an upper case
  • LCASE()                –             Used for returning a value to a lower case
  • MID()                    –             Used for extracting character(s) from within a given text
  • LEN()                     –             Used for returning the length of given value
  • ROUND()             –             Used for rounding a numeric value to the nearest decimal number
  • NOW()                  –             Used for returning the current date and time
  • FORMAT()           –             Used for formatting the field in a desired format

Demo Table

For establishing a clear understanding of the above functions, following Products table will be used:

ID Products Price
1 Keyboard 50
2 Mouse 50
3 LCD 100
4 Speaker 65.25
5 Printer 120

AVG() Function

AVG function is used to return the average of all values stored within a column

Syntax:

SELECT AVG(col_name) AS fld_name FROM tbl_ name;

Example:

SELECT AVG(Price) as Avg_Price FROM Products;

The above query will return the following:

Avg_Price

77.05

COUNT() Function

Count function is used to return the count of all values stored within a column

Syntax:

SELECT COUNT(col_name) AS fld_name FROM tbl_ name;

Example:

SELECT COUNT(*) as Count_Products FROM Products;

The above query will return the following:

Count_Products

5

TOP() Function

Top function is used to return the Top n value(s) of the specified column in the table. Where n is the specified number of records to return

Syntax:

SELECT TOP n (col_name) AS fld_name FROM tbl_ name;

Example:

SELECT TOP 1 (Products) as FIRST_Product FROM Products;

The above query will return the following:

FIRST_Product

Keyboard

MAX() Function

Max function is used to return the highest value among all values stored within a specified column

Syntax:

SELECT MAX(col_name) AS fld_name FROM tbl_ name;

Example:

SELECT MAX(Price) as MAX_Price FROM Products;

The above query will return the following:

MAX_Price

120

MIN() Function

Min function is used to return the lowest value among all values stored within a specified column

Syntax:

SELECT MIN(col_name) AS fld_name FROM tbl_ name;

Example:

SELECT MIN(Price) as MIN_Price FROM Products;

The above query will return the following:

MIN_Price

50

SUM() Function

Sum function is used to return the summation value of all the values stored within the specified column

Syntax:

SELECT SUM(col_name) AS fld_name FROM tbl_ name;

Example:

SELECT SUM(Price) as SUM_Price FROM Products;

The above query will return the following:

SUM_Price

385.25

UCASE() Function

UCase function is used to return the alphabetic value of a column in upper case

Syntax:

SELECT UCASE(col_name) AS fld_name FROM tbl_ name;

Example:

SELECT UCASE(Products) as UCase_Products FROM Products;

The above query will return the following:

UCase_Products
KEYBOARD
MOUSE
LCD
SPEAKER
PRINTER

LCASE() Function

LCase function is used to return the alphabetic value of a column in lower case

Syntax:

SELECT LCASE(col_name) AS fld_name FROM tbl_ name;

Example:

SELECT LCASE(Products) as LCase_Products FROM Products;

The above query will return the following:

LCase_Products
keyboard
mouse
lcd
speaker
printer

MID() Function

MID function is used to extract the desired character value of a desired length from a column

Syntax:

SELECT MID(col_name, start_at, length) AS fld_name FROM tbl_ name;

Example:

SELECT MID(Products, 2, 3) as MID_Products FROM Products;

The above query will return the following:

MID_Products
eyb
ous
cd
pea
rin

LEN() Function

Len function is used to return the length of a column value

Syntax:

SELECT LEN(col_name) AS fld_name FROM tbl_ name;

Example:

SELECT LEN(Products) as Len_Products FROM Products;

The above query will return the following:

Len_Products
8
5
3
7
7

ROUND() Function

Round function is used to return a rounded numerical value of a column

Syntax:

SELECT ROUND(col_name) AS fld_name FROM tbl_ name;

Example:

SELECT ROUND(Price) as Round_Price FROM Products;

The above query will return the following:

Round_Price

50

50

100

65

120

NOW() Function

Now function is used to return the current date and time

Syntax:

SELECT NOW() AS fld_name FROM tbl_ name;

Example:

SELECT NOW() as Current_DateTime FROM Products;

The above query will return the following:

Current_DateTime
2016-03-28 16:25:23

FORMAT() Function

Format function is used to return formatted value in a desired format style.

Syntax:

SELECT FORMAT(Value, Format) AS fld_name FROM tbl_ name;

Example:

SELECT FORMAT(NOW(), ‘DD-MM-YYYY’) as Current_DateTime FROM Products;

The above query will return the following:

Current_DateTime

28-03-2016

More Related Articles For You

    DBMS Tutorial

    SQL and DBMS Interview Questions with Answers

    DBMS and SQL Practice Tests , Questions and Quizzes