1. SUM() Function
SQL Server 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 the total price from table 'Stationary'.
| ID | Name | Quantity | Price |
|---|
| 1 | Pen | 10 | 200 |
| 2 | Ink | 35 | 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. AVG() Function
SQL Server AVG() function is used to compute average value of a numeric column.
Syntax
SELECT AVG (Column name )
FROM table_name
WHERE [Conditions]
Consider the following table titled 'Stationary', which contains the information of products.
| ID | Name | Quantity | Price |
|---|
| 1 | Pen | 10 | 200 |
| 2 | Ink | 35 | 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)
| Name | Price |
|---|
| Ink | 300 |
| Notebook | 400 |
3. COUNT() function
The SQL Server COUNT() function is used to return the number of rows, that match the given query.
Syntax
SELECT COUNT(expression)
FROM table_name
WHERE [conditions]
Consider the following table titled 'Stationary', which contains the information of products.
| ID | Name | Quantity | Price |
|---|
| 1 | Pen | 10 | 200 |
| 2 | Ink | 35 | 300 |
| 3 | Notebook | 20 | 400 |
| 4 | Pencil | 30 | 150 |
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.
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.
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.
4. MAX() Function
The SQL Server MAX() function returns the largest value of the selected column of a table.
Syntax:
SELECT MAX (Column_name) FROM table_name;
Example: Query using MAX() Function.
Consider the following table titled 'Stationary', which contains the information of products. Write a query to display the highest price from table 'Stationary'.
| ID | Name | Quantity | Price |
|---|
| 1 | Pen | 10 | 200 |
| 2 | Ink | 35 | 300 |
| 3 | Notebook | 20 | 400 |
| 4 | Pencil | 30 | 150 |
SELECT MAX(Price) AS HighPrice FROM Stationary
The result is shown in the following table.
5. SQL MIN() Function
The SQL Server MIN() function returns the smallest value of the selected column of a table.
SELECT MIN (Column_name) FROM table_name;
Example: Query using MIN() Function.
Consider the following table titled as 'Stationary', which contains the information of products. Write a query to display the Lowest price from table 'Stationary'.
| ID | Name | Quantity | Price |
|---|
| 1 | Pen | 10 | 200 |
| 2 | Ink | 35 | 300 |
| 3 | Notebook | 20 | 400 |
| 4 | Pencil | 30 | 150 |
SELECT MIN(Price) AS LowestPrice FROM Stationary
The result is shown in the following table.
