# SQL FUNCTIONS

## Introduction

SQL has a many built-in functions to compute string and numeric data.

The two types of SQL functions are:
1. SQL Aggregate functions
2. SQL Scalar functions

## SQL Aggregate functions

#### 1. SQL SUM() Function

SQL SUM() function is used to compute the total sum of a numeric column of a table.

Syntax:
SELECT SUM(Column_name) FROM table_name;

Example : Query using SUM() Function.
Consider the following table titled  'Stationary', which contains the information of products.

Write a query to display a Low price from table 'Stationary'.

IDNameQuantityPrice
1Pen10200
2Ink15300
3Notebook20400
4Pencil30150

SELECT SUM(Price) AS SUM FROM Stationary;

The result is shown in the following table.

SUM
1050

#### 2. SQL AVG() Function

SQL AVG() function is used to compute average value of a numeric column.

Syntax:
SELECT AVG (Column name )
FROM table_name
WHERE [Conditions];

Example : Query using AVG() Function.
Consider the following table titled 'Stationary', which contains the information of products.

IDNameQuantityPrice
1Pen10200
2Ink15300
3Notebook20400
4Pencil30150

i) Write a query to calculate average price of products

SELECT AVG(Price)
FROM Stationary;

The result is shown in the following table as:
AVG(Price)
262.5

ii) Write a query to display records(Name and price) of products which are greater than average price in the table.

SELECT Name, Price FROM Stationary
WHERE Price > (SELECT AVG(Price) FROM Stationary);

The result is shown in the following table.

NamePrice
Ink300
Notebook400

#### 3. SQL COUNT() function

The SQL COUNT() function is used to return the number of rows, that matches to given query.

Syntax:
SELECT COUNT(expression)
FROM table_name
WHERE [conditions];

Example : Query using COUNT() Function.
Consider the following table titled 'Stationary', which contains the information of products.

IDNameQuantityPrice
1Pen10200
2Ink15300
3Notebook20400
4Pencil30150

i) Write a query to count number of values of the column 'Name'

SELECT COUNT (Name)
FROM Stationary;

The result is shown in the following table.

COUNT(Name)
4

ii) SELECT COUNT(*) FROM Stationary
This query is used to return the number of records present in table.

The result is shown in the following table.

COUNT(*)
4

iii) SELECT COUNT(DISTINCT Column name) FROM table_name
This statement is used to count total distinct names of a specific table, as explained below.

SELECT COUNT(DISTINCT Name) FROM Stationary;

The result is shown in the following table.

COUNT(DISTINCT Name)
4