SQL: Aggregate Functions
In this post we will discuss aggregate functions. T-SQL support SUM, MIN, MAX, AVG, COUNT, SQRT, POWER and other aggregate functions.
- It ignores NULL inputs when applied to an expression.
- Use DISTINCT clause to remove duplicate values.
- In the group query the aggregate functions is applied per group, and returns the single value per group.
COUNT:
The COUNT() has three supported formats: COUNT(*), COUNT(expression), COUNT(DISTINCT exp).
COUNT(*):-
- Returns the number of rows in the table, including duplicates and rows containing NULL values in any column.
- Returns the result as the INT value.
Syntax: –
COUNT(*)
COUNT(expression):-
- Returns the number of rows containing not-null values that are in the column identified by expression.
- It ignores NULL values.
Syntax: –
COUNT(expression)
COUNT(DISTINCT exp):-
- Returns the number of unique, not-null rows that are in the column identified by expression.
Syntax: –
COUNT(DISTINCT exp)
SUM()
- Returns the sum of all the values or only the DISTINCT values in the expression.
Syntax: –
SUM([DISTINCT] exp)
- It is use only with numeric columns only.
MAX()
- Returns the maximum value in the expression.
Syntax: –
MAX([DISTINCT] exp)
MIN()
- Returns the minimum value in the expression.
Syntax: –
MIN([DISTINCT] exp)
AVG()
- Returns the average value in the group.
Syntax: –
AVG([DISTINCT] exp)
POWER()
- Returns the value of the specified expression to the specified power.
Syntax: –
POWER(exp, x)
- x is the power to which to raise expression.
Example:
DECLARE @exp1 int;
DECLARE @exp2 float;
SET @exp1= 4;
SET @exp2 = 4.5;
SELECT POWER(@exp1, 3) AS Output1, POWER(@exp2, 3) AS Output2;
Output:
|
Output1 |
Output2 |
|
64 |
91.125 |
SQRT()
- Returns the square root of the specified value.
Syntax: –
SQRT(exp)
Example:
DECLARE @sqrt int;
SET @sqrt=64;
SELECT SQRT(@sqrt) as Square_root;
Output:
|
Square_root |
|
8 |
EXP()
- Returns the e raise to the n-th power(n is the numeric expression), where e is the base of a natural algorithm and the value of e is 2.718.
Syntax: –
EXP(expression)
Example:
SELECT EXP(4) AS e_to_4s_power;
Output:
|
e_to_4s_power |
|
54.5981500331442 |
FLOOR()
- FLOOR() rounded up any positive or negative decimal value down to the next least integer value.
- It returns the smallest integer which is less than or equal to a specific numeric value.
Syntax: –
FLOOR(expression)
Example:
SELECT FLOOR(21.21) as smllest_integer;
Output:
|
smllest_integer |
|
21 |
CEILING()
- It returns the smallest integer which is greater than, or equal to the specified numeric value.
Syntax: –
CEILING(expression)
Example:
SELECT CEILING(21.21) as greater_integer;
Output:
|
greater_integer |
|
22 |
LOG()
- Returns the natural logarithm of the specified float expression.
Syntax: –
LOG(float_exp [,base])
- The base optional argument sets the base for the logarithm.
Example:
SELECT LOG(10) as logarithm;
Output:
|
logarithm |
|
2.30258509299405 |
Example:
Create table Items(ItemId int,Name varchar(50),Price decimal(18,2));
Insert into Items(ItemId,Name,Price) values(1,’Pen’,200),(2,’Pencil’,100),(1,’Pen’,200),(3,’Scale’,5);
Insert into Items(ItemId,Name) values (4,’Punch’);
select * from Items;
|
ItemId |
Name | Price |
| 1 | Pen |
200.00 |
|
2 |
Pencil | 100.00 |
| 1 | Pen |
200.00 |
|
3 |
Scale | 5.00 |
| 4 | Punch |
NULL |
Retrieve the number of rows in table
Select COUNT(*) as no_of_rows from Items;
Output:
|
no_of_rows |
|
5 |
Retrieve the number of ItemId in the table
Select COUNT(ItemId) as no_of_rows from Items;
Output:
|
no_of_rows |
|
5 |
Retrieve the number of distinct ItemId in the table
Select COUNT(DISTINCT ItemId) as no_of_distinct_rows from Items;
Output:
|
no_of_distinct_rows |
|
4 |
Retrieve the total price of all items in the table
Select SUM(Price)as total_price from Items;
Output:
|
Total_price |
|
505.00 |
Retrieve the minimum price of an item among all item
Select MIN(Price)as min_price from Items;
Output:
|
min_price |
|
5.00 |
Retrieve the maximum price of an item among all item
Select MAX(Price)as max_price from Items;
Output:
|
max_price |
|
200.00 |
Retrieve the average price of an item among all item
Select AVG(Price)as avg_price from Items;
Output:
|
avg_price |
|
126.250000 |
Keep Visiting TechwithR.com for more upcoming top technical articles and also see my new blog Mag4info. and for kotlin tutorial also see my blog kotlin category.
For online shopping visit BestBuy101.com
You can also by SQL 70-761 book write by me from Amazon.
You can also by Kotlin Book write by me from Amazon.
