1

Say we have 

SELECT        [DISTINCT]  target-list
FROM         relation-list
WHERE        qualification
GROUP BY  grouping-list
HAVING      group-qualification

,where the target-list contains (i) attribute names (ii) terms with aggregate operations (e.g., MIN (S.age)).

And the question is the textbook says that The attribute names (i) must be a subset of grouping-list.

But I have tried it on MYSQL database, which turns out to be ok.

Say we have

Programmer (pid, name, age), where pid is the key.
Experience (pid, lid, proficiency), where {pid,lid} is the key.

And now I try to perform following query to find out the number of records in Experience for each programmer, along with his name and pid:

Select P.pid, P.name, count(*) AS number
FROM Programmer P,  Experience E
WHERE E.pid = P.pid
GROUP BY p.pid

And this works fine and has the same result as using "GROUP BY p.pid, p.name"

So, is the textbook wrong? I really see no sense why the attribute names have to be a subset of grouping list.

Thanks!

Euclid Ye
  • 501
  • 5
  • 13
  • MySQL permits your usage, but most other RDBMS would report an error with this query. And in your particular case when only one table's columns are being returned, the result will be predictable, but in many cases MySQL's value returned for the ungrouped column will be indeterminate/unreliable. So some of us consider this a bad habit to get into in MySQL. The [question linked at the top](http://stackoverflow.com/questions/1023347/mysql-selecting-a-column-not-in-group-by) has a few good explanations of what MySQL is doing with your query. – Michael Berkowski Nov 01 '15 at 12:19
  • For a reference to what Michael B commented see this: [MySQL Handling of GROUP BY](https://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html) – jpw Nov 01 '15 at 12:20

0 Answers0