Home Sql Mysql-example SQL Aggregate Functions Where Clause
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

SQL Aggregate Functions Where Clause

Advertisement
SQL Aggregate Functions Where Clause return you the aggregate sum of the records based on the condition specified in Where Clause condition.

SQL Aggregate Functions Where Clause

     

SQL Aggregate Functions Where Clause return you the aggregate sum of the records based on the condition specified in Where Clause condition. The Aggregate Function include Sum , Count, Max, Min and Average.

Understand with Example

The Tutorial illustrate an example from 'SQL Aggregate Functions Where Clause'. To understand and grasp the example we start with a table 'stu'. The create table keywords helps you to create a table Stu with given fieldnames and datatypes respectively. 

 

 

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 values 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(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(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(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(1,'Komal',10,5,65);
insert into Stu values(2,'Ajay',10,5,46);
insert into Stu values(3,'Rakesh',10,5,63);

Stu

+--------+----------+-----------+--------+-------+
| Id     | Name     | Class     | sub_id | marks |
+--------+----------+-----------+--------+-------+
| 1      | Komal    | 10        | 1      | 45    |
| 2      | Ajay     | 10        | 1      | 56    |
| 3      | Rakesh   | 10        | 1      | 67    |
| 1      | Komal    | 10        | 2      | 47    |
| 2      | Ajay     | 10        | 2      | 53    |
| 3      | Rakesh   | 10        | 2      | 57    |
| 1      | Komal    | 10        | 3      | 45    |
| 2      | Ajay     | 10        | 3      | 56    |
| 3      | Rakesh   | 10        | 3      | 67    |
| 1      | Komal    | 10        | 4      | 65    |
| 2      | Ajay     | 10        | 4      | 56    |
| 3      | Rakesh   | 10        | 4      | 37    |
| 1      | Komal    | 10        | 5      | 65    |
| 2      | Ajay     | 10        | 5      | 46    |
| 3      | Rakesh   | 10        | 5      | 63    |
+--------+----------+-----------+--------+-------+

Query:

The Query return the records that include id, name and sum of the marks as total marks from the name specified in Where Clause  of table 'stu'.

select id, name, sum(marks) as 'total marks' 
from stu where name in('komal','ajay','santosh','rakesh') group by id;

Result

+------+--------+-------------+
| id   | name   | total marks |
+------+--------+-------------+
| 1    | Komal  | 267         |
| 2    | Ajay   | 267         |
| 3    | Rakesh | 291         |
+------+--------+-------------+
Advertisement

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 Functions Where Clause  

Post your Comment


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