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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

More Related Articles For You

• Architecture of DBMS
• CODDS RULE DBMS
• Database Models in DBMS
• Relational DBMS Concepts
• Keys and Types of keys in Database
• Database Normalization
• Generalization, Specialization and Aggregation Concepts in DBMS
• ERD Diagram Tutorial with Examples in DBMS
• Introduction to SQL
• How to Create Query in SQL, Create Table, Delete Table and User Insert Info Statements
• Alter Query Command in SQL, Add, Modify and Drop Column in Table
• TCL Commands in SQL
• Truncate Query, Drop and Rename Query in SQL
• All DML Statement in SQL, Select Statement, Delete, Insert and Update Statement
• Data Control Language DCL Revoke and Grant Command in SQL
• Select Statement or Select Query in SQL Explained with Examples
• Distinct Keyword Explained in SQL
• WHERE Statement or WHERE Clause in SQL Statement Explained
• AND & OR Operators in SQL
• LIKE Operator in SQL
• ORDER BY Clause Sorting Explained in SQL
• Group By Clause in SQL
• Having Clause Explained in SQL
• SQL Constraints
• SQL Aliases Use and Purpose in SQL
• SQL Joins Types, Use and Purpose
• SQL Sequence Syntax and Use with Examples
• SQL View
• SET Operation in SQL, UNION, UNION ALL, Intersect and Minus

    DBMS Tutorial

    SQL and DBMS Interview Questions with Answers

    DBMS and SQL Practice Tests , Questions and Quizzes