Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Combining aggregated and raw results - GROUP BY #190

pgmccann opened this issue Aug 22, 2017 · 0 comments

Combining aggregated and raw results - GROUP BY #190

pgmccann opened this issue Aug 22, 2017 · 0 comments


Copy link

@pgmccann pgmccann commented Aug 22, 2017

[Currently completing Instructor checkout]

In Aggregation, the unexpected behaviour encountered when combining aggregate and raw results in the absence of a GROUP BY clause is described starting at line 118:

We can also combine aggregated results with raw results, although the output might surprise you:
SELECT person, count(*) FROM Survey WHERE quant='sal' AND reading<=1.0;
person count(*)
lake 7
Why does Lake’s name appear rather than Roerich’s or Dyer’s? The answer is that when it has to aggregate a field, but isn’t told how to, the database manager chooses an actual value from the input set. It might use the first one processed, the last one, or something else entirely.

The episode then moves on to a discussion of what happens when there are no values to aggregate and the handling of null values before circling back to the problem of combining aggregate and raw data and introducing the GROUP BY clause.

I suggest that the section quoted above be removed, and the reference to this behaviour in the later section:

We know that this doesn't work:
because the database manager selects a single arbitrary scientist's name rather than aggregating separately for each scientist.

be expanded to include a fuller explanation. In this way, the solution to the problem is presented immediately, rather than leaving a thread dangling while we look at other issues.

It may also be worth noting that some database managers won't execute a query that combines raw and aggregate data unless a GROUP BY clause is included.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants
You can’t perform that action at this time.