0

My question is related to the semantic of SQL's GROUP BY primitive. Suppose that we have the following relation (Users): 

+----+------+---------------+-----+
| id | name | country       | age |
+----+------+---------------+-----+
| 1  | Foo  | Brazil        | 20  |
| 2  | Bar  | Japan         | 20  |
| 3  | Baz  | Brazil        | 22  |
| 4  | Quz  | Brazil        | 18  |
+----+------+---------------+-----+

I expected that the following dummy query:

SELECT name, country, avg(age)
FROM Users
GROUP BY country

returns four tuples, since we used a non-grouped attribute in the SELECT projection:

+------+---------------+-----+
| name | country       | age |
+------+---------------+-----+
| Foo  | Brazil        | 20  |
| Bar  | Japan         | 20  |
| Baz  | Brazil        | 20  |
| Quz  | Brazil        | 20  |
+------+---------------+-----+

Yet, it yields the following tuples:

+------+---------------+-----+
| name | country       | age |
+------+---------------+-----+
| Foo  | Brazil        | 20  |
| Bar  | Japan         | 20  |
+------+---------------+-----+

Why?

Edit: found out that Why can't you mix Aggregate values and Non-Aggregate values in a single SELECT? answers this question.

Community
  • 1
  • 1
Marcos Roriz Junior
  • 4,076
  • 11
  • 51
  • 76
  • 2
    This should have thrown an error due to the lack of `name` being in the `GROUP BY` clause... what DBMS is this? – Siyual Oct 30 '15 at 19:26
  • 4
    Mysql,for example, allows indeterminate results when you dont use an aggregate function on a column not in the GROUP BY.Almost all others dbs throw an error – Mihai Oct 30 '15 at 19:28
  • @Siyual I did not test on a real database, since I do not have access to one here. I *tested* on w3schools online db, and it returned these tuples. My question is more related to what is the expected outcome? Is it an error? – Marcos Roriz Junior Oct 30 '15 at 19:37
  • 1
    It seems that my questoin has been answered here : http://stackoverflow.com/questions/5920070/why-cant-you-mix-aggregate-values-and-non-aggregate-values-in-a-single-select. I'll close the question – Marcos Roriz Junior Oct 30 '15 at 19:39
  • let me guess... mySQL database? read up on mySQL extensions.. it extends group by's so that if you don't put non-aggregated fields in the group by, the system is free to choose any value. that's right.. ANY. https://dev.mysql.com/doc/refman/5.1/en/group-by-handling.html "The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate." – xQbert Oct 30 '15 at 20:04

0 Answers0