Aggregate Function In SQL

Introduction

 
The basic need of the aggregate function is to get the single value in output from more than one input variables. We can use 'GROUP BY' and 'HAVING' clauses with an Aggregate Function.
 
Following are the basic aggregate functions..
  • AVG() 
  • COUNT()
  • MIN()
  • MAX()
  • SUM() 
Syntax
To perform an aggregate function, we need to follow the following syntax.
 
aggregate_function (DISTINCT | ALL expression) 
 
Example 
 
To perform database aggregate function, we need some records in the database. So, here, I will create a table and insert some records into the table.
 
Query for creating a table -
  1. CREATE TABLE [dbo].[StudentTable](  
  2.     [ID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Section] [varchar](5) NULL,  
  4.     [RollNumber] [intNULL,  
  5.     [Marks] [intNULL,  
  6.  CONSTRAINT [PK_StudentTable] PRIMARY KEY CLUSTERED   
  7. (  
  8.     [ID] ASC  
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  10. ON [PRIMARY]  
  11. GO   
Query for inserting records to the above table -
  1. insert into StudentTable (Section,RollNumber,Marks) values ('c',101,543)  
  2. insert into StudentTable (Section,RollNumber,Marks) values ('c',102,524)  
  3. insert into StudentTable (Section,RollNumber,Marks) values ('c',103,559)  
  4. insert into StudentTable (Section,RollNumber,Marks) values ('B',101,452)  
  5. insert into StudentTable (Section,RollNumber,Marks) values ('B',102,324)  
  6. insert into StudentTable (Section,RollNumber,Marks) values ('B',103,543)  
  7. insert into StudentTable (Section,RollNumber,Marks) values ('A',101,450)  
  8. insert into StudentTable (Section,RollNumber,Marks) values ('A',102,480)  
  9. insert into StudentTable (Section,RollNumber,Marks) values ('A',103,470)  
Now, the table looks like below.
 
 
 
Now, we will perform the aggregate function tasks.
 

AVG()

 
AVG() is used for getting the average value.
 
Query 1
  1. select AVG(Marks) as 'Average Marks' from StudentTable   
OUTPUT
 
482
 
The above query will return the average marks from all the marks.
 
Query 2
  1. select AVG(Marks) as 'Average Marks' from StudentTable GROUP BY Section  
OUTPUT
 
466,439,542
 
The above query will return the average marks grouping of sections. The returned marks are respectively by section A, B, and C.
 

COUNT()

 
COUNT() is used to count the value. It means it will calculate how many rows are available.
 
Query 1
  1. select COUNT(Marks) as 'Count Marks' from StudentTable  
OUTPUT
 
9
 
The above query will count all the marks.
 
Query 2
  1. select COUNT(Marks) as 'Count Marks' from StudentTable GROUP BY Section  
OUTPUT
 
3,3,3
 
The above query will count all the marks grouping of sections. The returned statements of sections are respectively  A, B, and C.
 

MIN()

 
MIN() is used to return the minimum value of the statement. 
 
Query 1
  1. select MIN(Marks) as 'Min Marks' from StudentTable   
OUTPUT
 
324
 
The above query returned the minimum marks among them all.
 
Query 2
  1. select MIN(Marks) as 'Min Marks' from StudentTable GROUP BY Section  
OUTPUT
 
450,324,524
 
The above query returned the minimum marks from the statement grouping of sections. The sections are respectively A, B, and C.
 

MAX()

 
MAX() is used to return the maximum value of the statement.
 
Query 1
  1. select MAX(Marks) as 'Max Marks' from StudentTable  
OUTPUT
 
559
 
The above query returned the maximum mark from all the marks.
 
Query 2
  1. select MAX(Marks) as 'Max Marks' from StudentTable GROUP BY Section  
OUTPUT
 
480,543,559
 
The above query returned the maximum marks from the statement grouping of sections. The sections are respectively A, B, and C.
 

SUM()

 
SUM() is used to return the sum value of the statement.
 
Query 1
  1. select SUM(Marks) as 'Sum Marks' from StudentTable  
OUTPUT
 
4345
 
The above query returned the sum of all the marks.
 
Query 2
  1. select SUM(Marks) as 'Sum Marks' from StudentTable GROUP BY Section  
OUTPUT
 
1400,1319,1626
 
The above query returned the sum of marks grouping of sections, the sections are respectively A, B, and C.
 

Summary

 
In this session, I discussed the implementation and use of the aggregate functions in SQL. I hope this session will help the beginners and boost their learning.  
 
I am happy to help you.


Similar Articles