Home Sql Mysql-example SQL Aggregate Queries
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

SQL Aggregate Queries

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

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)
Advertisements

Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: January 23, 2009

Ask Questions?    Discuss: SQL Aggregate Queries   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
DMCA.com