Aggregate Functions in SQL Server
Aggregate function in SQL is used to perform the calculations on multiple rows of a single column of a table. It returns a single value.
Types of Aggregate functions in SQL:
1.AVG Function
AVG function in SQL is used to find out the average of a field in various records.
Syntax
AVG()
or
AVG( [ALL|DISTINCT] expression )
Product | Company | Quantity | Rate | Cost |
---|---|---|---|---|
Item1 | Com1 | 2 | 10 | 20 |
Item2 | Com2 | 3 | 15 | 45 |
Item3 | Com2 | 4 | 20 | 100 |
Item4 | Com3 | 1 | 50 | 50 |
Item5 | Com3 | 7 | 35 | 245 |
Let us use the same table for checking out all the functions:
Now,we will check how average function works
SELECT AVG(COST)
FROM PRODUCT_MAST;
Output :92
2.COUNT Function
COUNT function in SQL is used to Count the number of rows in a database table. It can work on both numeric and non-numeric data types.
COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. COUNT(*) considers duplicate and Null.
COUNT(*)
or
COUNT( [ALL|DISTINCT] expression )
SELECT COUNT(*)
FROM PRODUCT_MAST;
Output :5
SELECT COUNT(*)
FROM PRODUCT_MAST;
WHERE RATE>=20;
Output :4
SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY;
Output :
Com1 1
Com2 2
Com3 2
SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY
HAVING COUNT(*)>2;
Output :
Com1 1
Com2 2
3.MIN Function
MIN function in SQL is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.
MIN()
or
MIN( [ALL|DISTINCT] expression )
Example
SELECT MIN(RATE)
FROM PRODUCT_MAST;
Output :10
MAX function in SQL is used to find the maximum value of a certain column. This function determines the largest value of all selected values of a column.
MAX()
or
MAX( [ALL|DISTINCT] expression )
Example
SELECT MAX(RATE)
FROM PRODUCT_MAST;
Output :50
Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.
Syntax
SUM()
or
SUM( [ALL|DISTINCT] expression )
Example: SUM()
SELECT SUM(COST)
FROM PRODUCT_MAST;
Output :460
Example: SUM() with WHERE
SELECT SUM(COST)
FROM PRODUCT_MAST
WHERE QTY>3;
Output :345
SELECT SUM(COST)
FROM PRODUCT_MAST
WHERE QTY>3
GROUP BY COMPANY;
Output :
Com2 100
Com3 245
SQL Server Connection String Formats
posted on 2019-08-27 05:53:53 - SQL Server Tutorials