Aggregate function in SQL Server

Aggregate function in SQL Server

SQL aggregate functions are used to sum, count, get the average, get the minimum and get the maximum values from a column or from a sub-set of column values. SQL aggregate functions return a single value, calculated from values in a column.

Kinds of Aggregate function:

·         AVG() - Returns the average value

·         COUNT() - Returns the number of rows

·         MAX() - Returns the largest value

·         MIN() - Returns the smallest value

·         SUM() - Returns the sum

In this article illustrates an example on 'SQL Aggregate Queries'. To understand example we create a table 'Student ' with required field names and datatypes.

Table create query

create table Student

(

       Id varchar(4),

       Name varchar(15),

       Class  varchar(10),

       Subject_Id varchar(2),

       Marks int

);

Student

AVG ( )

The SQL  AVG () function is used to find average, in above table if we want to find average of student marks we can find easily average of students marks  with the help of AVG () function as shown below:

Query

select id, avg(Marks)as 'Average' from Student group by (Id)

Result

In above screen shot you can see average is showing of each student.

COUNT ( )

The SQL COUNT () function returns the number of rows in a table satisfying the criteria. If we want to count how many student records available in table we will use the following SQL COUNT expression:

Query

select Id, count(id)as 'Total Paper' from Student group by (Id)

Result

In above screen shot you can see each student attempted how many papers.

MAX ( )

The SQL MAX () function is used to return maximum record values from table.

Query

Select id,Max(Marks)as'Maximum Numbers'  from Student group by(Id)

Result

In above screen shot you can see each student maximum numbers.

MIN ( )

The SQL MAX () function is used to return minimum record values from table.

Query

Select id,MIN(Marks)as'Minimum Numbers' from Student group by(Id)

In above screen shot you can see each student minimum numbers.

SUM ( )

The SUM () function return the sum of marks records as total marks from 'Student' table with the specified criteria in group by clause.

Query

Select id,sum(Marks)as 'Total Marks' from Student group by(Id)

Result

In above screen shot you can see each student total marks.