You are here
Home > Sql server > Sql server 2016 > SQL: Aggregate Functions

SQL: Aggregate Functions

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.

Phone

Leave a Reply

Top