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.
| SUM |
|---|
| 1050 |
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]
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 | 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:
| 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.
| 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]
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 | 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.
| COUNT(Name) |
|---|
| 4 |
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 |
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 |
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.
| HighPrice |
|---|
| 400 |
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.
| LowestPrice |
|---|
| 150 |
