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'.
| ID | Name | Quantity | Price |
|---|
| 1 | Pen | 10 | 200 |
| 2 | Ink | 15 | 300 |
| 3 | Notebook | 20 | 400 |
| 4 | Pencil | 30 | 150 |
SELECT SUM(Price) AS SUM FROM Stationary;
The result is shown in the following table.
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.
| ID | Name | Quantity | Price |
|---|
| 1 | Pen | 10 | 200 |
| 2 | Ink | 15 | 300 |
| 3 | Notebook | 20 | 400 |
| 4 | Pencil | 30 | 150 |
i) Write a query to calculate average price of products
SELECT AVG(Price)
FROM Stationary;
The result is shown in the following table as:
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.
| Name | Price |
|---|
| Ink | 300 |
| Notebook | 400 |
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.
| ID | Name | Quantity | Price |
|---|
| 1 | Pen | 10 | 200 |
| 2 | Ink | 15 | 300 |
| 3 | Notebook | 20 | 400 |
| 4 | Pencil | 30 | 150 |
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.
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.
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.
