- Recall that the
GROUP BY clause can
form groups
based on
attribute values on
the tuples.
Example: group the employee tuples based on their values for the sex attribute:
- After forming groups
based on their attribute values,
you can also specify a
condition on each group
Example: suppose we only want to consider groups where the size of the group is at least 2:
Group condition:
- A group condition is
a condition that applies to
a group formed by the
GROUP BY clause
- A group condition will filter out (remove) (entire) groups that does not satisfy the group condition
- A group condition is
a condition that applies to
a group formed by the
GROUP BY clause
-
Specifying a group condition
in SQL:
- The
group condition
is specified using the
HAVING clause
following the
GROUP BY clause:
SELECT grouping-attributes or set-functions FROM relation-list WHERE tuple-boolean-condition GROUP BY grouping-attribute-list HAVING group-boolean-condition
Note:
- The above syntax structure is
the structure of the
most general SQL command
(It has every possible clause of an SQL command included in the syntax.....)
- The
group condition
is specified using the
HAVING clause
following the
GROUP BY clause:
- Syntax of
of a group condition:
SELECT grouping-attributes or set-functions FROM relation-list WHERE tuple-boolean-condition GROUP BY grouping-attribute-list HAVING SetFunction( .. ) RelOp ... <---- Group condition
RelOp is a a relational operator (such as <, ≤, > ≥, =, ≠)
- The HAVING
group condition:
- The group (boolean) condition (specified using the HAVING clause) is a condition on a group of tuples
- Example:
The employee relation: +--------+---------+-----+ | fname | lname | dno | +--------+---------+-----+ | James | Borg | 1 | | Alicia | Zelaya | 4 | | Jennif | Wallace | 4 | | Ahmad | Jabbar | 4 | | John | Smith | 5 | | Frankl | Wong | 5 | | Ramesh | Narayan | 5 | | Joyce | English | 5 | +--------+---------+-----+
For groups based on dno ; and count the number of employees in each group: select dno, count(*) from employee group by dno +-----+----------+ | dno | count(*) | +-----+----------+ | 1 | 1 | | 4 | 3 | | 5 | 4 | +-----+----------+
Retrieve groups that has >= 2 elements: select dno, count(*) from employee group by dno having count(*) > 2 +-----+----------+ | dno | count(*) | +-----+----------+ | 4 | 3 | | 5 | 4 | +-----+----------+
- The processing of the
SQL SELECT command
with
GROUP BY-HAVING
claus is as follows:
- Execute the
basic query
with the tuple condition:
SELECT ... FROM relation-list WHERE tuple-boolean-condition
This query will select a set of tuples that satisfy the tuple (WHERE) condition
-
Group
the selected tuples
according to their values
in the
grouping attributes :
The grouping attributes are given in the GROUP BY clause:
GROUP BY grouping-attribute-list
-
Select the
groups that
satisfies
the group condition
given in the
HAVING clause:
HAVING group-condition
The group condition is applied to each group:
- Groups that do not satisfy the HAVING group condition are removed)
- The
SELECT clause
may contain set functions:
- These set functions are applied to the selected groups
- Execute the
basic query
with the tuple condition:
- Example Query processing:
Query: SELECT dno, sum(salary) FROM employee WHERE sex = 'M' GROUP BY dno HAVING count(*) > 2 - How is the query
processed:
The employee relation: ssn fname lname sex dno --------- ------ -------- --- ----------- 123456789 John Smith M 5 333445555 Frankl Wong M 5 999887777 Alicia Zelaya F 4 987654321 Jennif Wallace F 4 666884444 Ramesh Narayan M 5 453453453 Joyce English F 5 987987987 Ahmad Jabbar M 4 888665555 James Borg M 1
1. Execute the basic query SELECT * FROM employee WHERE sex = 'M' Result: ssn fname lname sex dno --------- ------ -------- --- ----------- 123456789 John Smith M 5 333445555 Frankl Wong M 5 666884444 Ramesh Narayan M 5 987987987 Ahmad Jabbar M 4 888665555 James Borg M 1
2. Group the selected tuples by DNO: ssn fname lname sex dno --------- ------ -------- --- ----------- 123456789 John Smith M 5 333445555 Frankl Wong M 5 666884444 Ramesh Narayan M 5 987987987 Ahmad Jabbar M 4 888665555 James Borg M 1
3. Select the groups that satisfy: "having count(*) > 2 ssn fname lname sex dno --------- ------ -------- --- ----------- 123456789 John Smith M 5 333445555 Frankl Wong M 5 666884444 Ramesh Narayan M 5987987987 Ahmad Jabbar M 4(COUNT(*) <=2)888665555 James Borg M 1(COUNT(*) <=2)
4. The SELECT expression are computed: SELECT dno, sum(salary) Result: dno sum(salary) ----------- ------------------------------- 5 108900.00