SQL Aggregate Queries

SQL Aggregate Queries show you the list of Aggregate Function that include Sum, Count, Average, Maximum, Minimum etc.

Understand with Example

The Tutorial illustrate an example from 'SQL Aggregate Queries'. To understand example we create a table 'Stu' with required fieldnames and datatypes required.

Create Table Stu:

create table Stu(Id varchar(2), Name varchar(15), 
Class  varchar(10),sub_id varchar(2),marks varchar(3));

Insert data into Stu:

The insert into is used to add the records or rows to the table 'Stu'.

insert into Stu values(1,'Komal',10,1,45);
insert into Stu values(2,'Ajay',10,1,56);
insert into Stu values(3,'Rakesh',10,1,67);
insert into Stu values(4,'Santosh',10,1,67);

insert into Stu values(1,'Komal',10,2,47);
insert into Stu values(2,'Ajay',10,2,53);
insert into Stu values(3,'Rakesh',10,2,57);
insert into Stu values(4,'Santosh',10,2,67);
insert into Stu values(5,'Bhanu',10,2,67);

insert into Stu values(1,'Komal',10,3,45);
insert into Stu values(2,'Ajay',10,3,56);
insert into Stu values(3,'Rakesh',10,3,67);
insert into Stu values(4,'Santosh',10,3,67);
insert into Stu values(5,'Bhanu',10,3,67);

insert into Stu values(1,'Komal',10,4,65);
insert into Stu values(2,'Ajay',10,4,56);
insert into Stu values(3,'Rakesh',10,4,37);
insert into Stu values(4,'Santosh',10,4,67);
insert into Stu values(5,'Bhanu',10,2,67);

insert into Stu values(1,'Komal',10,5,65);
insert into Stu values(2,'Ajay',10,5,46);
insert into Stu values(3,'Rakesh',10,5,63);

AVG Functions :

The AVG (average) function is used to provide the mathematical average of a series of values.

mysql> select id, name, avg(marks) as
    -> 'avg marks' from stu  group by id;
+------+---------+-----------+
| id   | name    | avg marks |
+------+---------+-----------+
| 1    | Komal   | 53.4      |
| 2    | Ajay    | 53.4      |
| 3    | Rakesh  | 58.2      |
| 4    | Santosh | 67        |
| 5    | Bhanu   | 67        |
+------+---------+-----------+
5 rows in set (0.00 sec)

COUNT Functions:

The Count Functions is used to provide the number of records in a table which matches with a given criteria in table.

mysql> select id, name, count(id) as 'paper'
    -> from stu  group by id;
+------+---------+-------+
| id   | name    | paper |
+------+---------+-------+
| 1    | Komal   | 5     |
| 2    | Ajay    | 5     |
| 3    | Rakesh  | 5     |
| 4    | Santosh | 4     |
| 5    | Bhanu   | 3     |
+------+---------+-------+
5 rows in set (0.00 sec)

MIN Functions :

The Min Function return the minimum marks of the records from table stu.

mysql> select id, name, min(marks) as
    -> 'Min marks in sub' from stu  group by id;
+------+---------+------------------+
| id   | name    | Min marks in sub |
+------+---------+------------------+
| 1    | Komal   | 45               |
| 2    | Ajay    | 46               |
| 3    | Rakesh  | 37               |
| 4    | Santosh | 67               |
| 5    | Bhanu   | 67               |
+------+---------+------------------+
5 rows in set (0.00 sec)

MAX Functions :

The Max Functions is used to return the maximum records value from table stu.

mysql> select id, name, max(marks) as
    -> 'Max marks in sub' from stu  group by id;
+------+---------+------------------+
| id   | name    | Max marks in sub |
+------+---------+------------------+
| 1    | Komal   | 65               |
| 2    | Ajay    | 56               |
| 3    | Rakesh  | 67               |
| 4    | Santosh | 67               |
| 5    | Bhanu   | 67               |
+------+---------+------------------+
5 rows in set (0.00 sec)

SUM Functions:

The SUM Function return the sum of marks records as total marks from table 'stu' that matches with the specified criteria in group by clause.

mysql> select id, name, sum(marks) as 'total marks'
    -> from stu  group by id;
+------+---------+-------------+
| id   | name    | total marks |
+------+---------+-------------+
| 1    | Komal   | 267         |
| 2    | Ajay    | 267         |
| 3    | Rakesh  | 291         |
| 4    | Santosh | 268         |
| 5    | Bhanu   | 201         |
+------+---------+-------------+
5 rows in set (0.00 sec)

SQL Aggregate Queries

SQL Aggregate Queries

SQL Aggregate Queries

Tutorials