Home Quartz SQL HAVING Clause
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

SQL HAVING Clause

Advertisement
This page discusses - SQL HAVING Clause

SQL HAVING Clause

     

Having clause is used with the select clause to specify a search condition for a group or aggregate. The HAVING clause works with where clause, but it is implied on group only-represent the rows in the result set. The Where clause is applicable to individual rows. 

Create Table Stu_Table

The Tutorial illustrates an example from SQL HAVING Clause. In this example, we create  a table 'Stu_Table'. The create table statement is used to create a table Stu_Table.

SQL statement to create table:

 

 

 

create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(15), 
Stu_Class  varchar(10), sub_id varchar(2), marks varchar(3));

Insert Data into Stu_Table

The insert into add a rows or records to a table 'Stu_Table'.

SQL statement to insert data into table:

insert into Stu_Table values(1,'Komal',10,1,45);
insert into Stu_Table values(2,'Ajay',10,1,56);
insert into Stu_Table values(3,'Rakesh',10,1,67);
insert into Stu_Table values(1,'Komal',10,2,47);
insert into Stu_Table values(2,'Ajay',10,2,53);
insert into Stu_Table values(3,'Rakesh',10,2,57);
insert into Stu_Table values(1,'Komal',10,3,45);
insert into Stu_Table values(2,'Ajay',10,3,56);
insert into Stu_Table values(3,'Rakesh',10,3,67);
insert into Stu_Table values(1,'Komal',10,4,65);
insert into Stu_Table values(2,'Ajay',10,4,56);
insert into Stu_Table values(3,'Rakesh',10,4,37);
insert into Stu_Table values(1,'Komal',10,5,65);
insert into Stu_Table values(2,'Ajay',10,5,46);
insert into Stu_Table values(3,'Rakesh',10,5,63);

Stu_Table

The select return you the records from a table 'Stu_Table'.

Records in the table:

+--------+----------+-----------+--------+-------+
| Stu_Id | Stu_Name | Stu_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 SQL Syntax is used to retrieve the name of the candidate whose average score is greater than 50.The HAVING  clause restrict your records from a table. The group by statement is used  with the SQL aggregate functions to group the retrieved data based upon one or more columns.

select stu_id, stu_name, GROUP_CONCAT(marks) as marks, 
sum(marks)as total ,avg(marks) as per
from stu_table group by stu_id HAVING avg(marks)>50;

Result

+--------+----------+----------------+-------+------+
| stu_id | stu_name | marks          | total | per  |
+--------+----------+----------------+-------+------+
| 1      | Komal    | 45,65,47,65,45 | 267   | 53.4 |
| 2      | Ajay     | 46,56,56,53,56 | 267   | 53.4 |
| 3      | Rakesh   | 67,57,37,67,63 | 291   | 58.2 |
+--------+----------+----------------+-------+------+

Query

select stu_id, stu_name,GROUP_CONCAT(marks) as marks, 
sum(marks)as total ,avg(marks) as per
from stu_table group by stu_id HAVING avg(marks)>55;

Result

+--------+----------+----------------+-------+------+
| stu_id | stu_name | marks          | total | per  |
+--------+----------+----------------+-------+------+
| 3      | Rakesh   | 67,57,37,67,63 | 291   | 58.2 |
+--------+----------+----------------+-------+------+
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: April 18, 2011

Ask Questions?    Discuss: SQL HAVING Clause  

Post your Comment


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