Home Sql SQL HAVING Clause

SQL HAVING Clause
Posted on: December 20, 2008 at 12:00 AM

Having clause is used with the select clause to specify a search condition for a group or aggregate.

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 |
+--------+----------+----------------+-------+------+

Related Tags for SQL HAVING Clause:
c, search, select, div, io, sed, vi, aggregate, id, set, app, group, row, if, condition, for, rows, ie, cab, where, with, work, to, avi, ci, ws, ear, e, it, li, use, ul, result, pe, im, in, m, having, nt, clause, ca, arc, cl, es, spec, rc, pp, s, su, sp, at, k, individual, is, ha, iv, mpl, pre, gre, ea, du, dual, ar, repr, vin, s, s, th, cond, av, ab, abl, ap, ga, gat, ica, ica, pl, pr, nd, only, on, o, nl

«Previous Index Next»