Grouping Functions in SQL

Grouping Functions :- Grouping Functions operate on sets of rows to give one result per group. Unlike single row functions, group functions operate on sets of rows to give one result per group. These sets may be full Table or Table split into groups .


TYPES OF GROUP FUNCTIONS :-

1. MIN( )                  2. MAX( )              3. SUM( )                   4. AVG( )              5. COUNT( )


Guidelines for using Group Functions :-

*  DISTINCT makes function consider only Non-duplicate values . If we use DISTINCT in our queries it take duplicate records only once
* All group Functions Except COUNT ( * )  ignore null values.
* When we use Group By clause it will produce result in Asc order by default and we can use Desc to make it in descending order.
_______________________________________________

1. MIN ( ) :- MIN ( ) function returns the minimum value of an expression , It does not take null values.

SYNTAX :-                    MIN ( DISTINCT (Expression ))

 * Here, Distinct keyword will not take duplicate records and eliminate them from output




Example :-   

select * from student where age = ( select min( age ) from student ) 

* This is a sub-Query. A Sub-Query is a query within a query. Then query inside the inner brackets is called inner query and the query that contains the inner query within its brackets is called outer query. 
* First, the inner query is executed and result of inner query is given as input to outer query 
* In this query first inner query is executed and it gets the minimum value of age field in table 
* Outer query gets minimum age from inner query and show record of student with minimum age from table

Output :- It display the record minimum age student



2. MAX ( ) :- MAX ( ) function returns the maximum value of an expression , It does not take null values.

SYNTAX :-                    MAX ( DISTINCT (Expression ))

 * Here, Distinct keyword will not take duplicate records and eliminate them from output

Example :-   



select * from student where age = ( select max( age ) from student ) 

* This is a sub-Query. A Sub-Query is a query within a query. Then query inside the inner brackets is called inner query and the query that contains the inner query within its brackets is called outer query. 
* First, the inner query is executed and result of inner query is given as input to outer query 
* In this query first inner query is executed and it gets the maximum value of age field in table 
* Outer query gets maximum age from inner query and show record of student with maximum age from table

Output :- It display the record maximum age student



3 . SUM ( ) :- SUM ( ) function perform the sum or addition of all values in given field. It ignores Null values.

Syntax :-               SUM ( DISTINCT ( FIELD-NAME))
Example :-

select * 
From student

select sum ( age )
From student

Output  :- It shows the sum of all values in age fields




4 . AVG ( ) :- AVG ( ) function perform the sum or addition of all values in given field and then divide it by number of values in that given field and gives average of values of that field. It ignores Null values.

Syntax :-               AVG ( DISTINCT ( FIELD-NAME))
Example :-

select * 
From student

select avg ( age )
From student

5. Count ( ) :- Count ( ) function counts the number of rows in specified field of table . It also includes the duplicates values in the fields or duplicate rows of fields. 

count ( DISTINCT ( FIELD-NAME)) will provide the number of rows in particular field and it does not count duplicate values in fields.

Syntax :-              

count (( FIELD-NAME)) 
count ( DISTINCT ( FIELD-NAME))

Example 1:-

select * 
From student

select count ( age )
From student

Output :- It takes duplicate rows in count

Example 2:-

select * 
From student

select count (DISTINCT ( age ))
From student

Output :-  It does not take duplicate rows in count
 

Share this

heemanshu bhalla