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 |
+--------+----------+----------------+-------+------+
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
