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 -
- CREATE TABLE [dbo].[StudentTable](
- [ID] [int] IDENTITY(1,1) NOT NULL,
- [Section] [varchar](5) NULL,
- [RollNumber] [int] NULL,
- [Marks] [int] NULL,
- CONSTRAINT [PK_StudentTable] PRIMARY KEY CLUSTERED
- (
- [ID] ASC
- )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
- ) ON [PRIMARY]
- GO
Query for inserting records to the above table -
- insert into StudentTable (Section,RollNumber,Marks) values ('c',101,543)
- insert into StudentTable (Section,RollNumber,Marks) values ('c',102,524)
- insert into StudentTable (Section,RollNumber,Marks) values ('c',103,559)
- insert into StudentTable (Section,RollNumber,Marks) values ('B',101,452)
- insert into StudentTable (Section,RollNumber,Marks) values ('B',102,324)
- insert into StudentTable (Section,RollNumber,Marks) values ('B',103,543)
- insert into StudentTable (Section,RollNumber,Marks) values ('A',101,450)
- insert into StudentTable (Section,RollNumber,Marks) values ('A',102,480)
- 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
- select AVG(Marks) as 'Average Marks' from StudentTable
OUTPUT
482
The above query will return the average marks from all the marks.
Query 2
- 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
- select COUNT(Marks) as 'Count Marks' from StudentTable
OUTPUT
9
The above query will count all the marks.
Query 2
- 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
- select MIN(Marks) as 'Min Marks' from StudentTable
OUTPUT
324
The above query returned the minimum marks among them all.
Query 2
- 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
- select MAX(Marks) as 'Max Marks' from StudentTable
OUTPUT
559
The above query returned the maximum mark from all the marks.
Query 2
- 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
- select SUM(Marks) as 'Sum Marks' from StudentTable
OUTPUT
4345
The above query returned the sum of all the marks.
Query 2
- 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.