Important effect of "group by" on
common attribute values
Important effect of the
GROUP BYclause:
IF you use a
GROUP BYclause,
then:
The attributes
used in the
SELECT listmust be:
a subset of the
grouping attributes
a set function
Example:
If you use
the followingGROUP BY clause:
SELECT ....
FROM ....
WHERE
GROUP BY Attr1, Attr2, Attr3
Then:
the
attribute in the
SELECT list
must be:
Attr1
Attr2
Attr3or
a set function
Example:
SELECT Attr1, Attr2, Attr3, SUM(Attr4), MAX(Attr5)
FROM ....
WHERE
GROUP BY Attr1, Attr2, Attr3
You cannot use this:
SELECT Attr1, Attr2, Attr3, Attr4, SUM(Attr4), MAX(Attr5)
FROM ....
WHERE
GROUP BY Attr1, Attr2, Attr3
Because Attr4 is
not one of the
grouping attributesAttr1,
Attr2 and
Attr3
Theory vs. implementation
Implementation note:
Some implementation of SQLallows the user to
specify
non-grouping attributes
in the SELECT-clause
In these implementations, the
value output by the
SELECT command is attribute value in
one of the tuples in the group.
Which tuple is
undefined....
Oracle Database System:
implements the GROUP BYcorrectly
SELECT ssn, sum(salary)
FROM employee
GROUP BY dno
Result:
SELECT ssn, sum(salary)
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression (ssn is not a GROUP BY attribute)
Oracle detects (correctly) that
ssn
is
not
one of the grouping attribute
and reports the
error
MySQL:
has a
faultyimplementation
of
GROUP BY syntax:
MySQL (version 5) has an
erroneous implementation
for the GROUP BY feature:
This bug is
fixed in
version 5.7.25 !!!
It allows you
to specify non-grouping attributes
in the SELECT clause !!!
Example:
This is the employee relation:
+--------+---------+-----+----------+
| fname | lname | dno | salary |
+--------+---------+-----+----------+
| John | Smith | 5 | 30000.00 |
| Frankl | Wong | 5 | 40000.00 |
| Ramesh | Narayan | 5 | 38000.00 |
| Joyce | English | 5 | 25000.00 |
| Alicia | Zelaya | 4 | 25000.00 |
| Jennif | Wallace | 4 | 43000.00 |
| Ahmad | Jabbar | 4 | 25000.00 |
| James | Borg | 1 | 55000.00 |
+--------+---------+-----+----------+
select fname, lname, dno, sum(salary)
from employee
group by dno
+--------+--------+-----+-------------+
| fname | lname | dno | sum(salary) |
+--------+--------+-----+-------------+
| James | Borg | 1 | 55000.00 | (What is so special about Borg ?)
| Alicia | Zelaya | 4 | 93000.00 | (What is so special about Zelaya ?)
| John | Smith | 5 | 133000.00 | (What is so special about Smith ?)
+--------+--------+-----+-------------+
It looks like that MySQL returns
the first tuple value that
it finds in each group
when the attribute is
not a
grouping attribute