Scribd
Upload
20 views1 page

Step 4: The GROUP BY Clause Is Applied To Partition The Rows Into Groups. at

The document discusses the GROUP BY clause in SQL queries. It explains that the GROUP BY clause partitions the candidate result set into groups based on the specified columns. Each group can initially contain multiple rows but will later be reduced to a single row. The example shows the candidate result set being grouped into six groups based on the values in columns t1.key_1 and t3.key_1.

Uploaded by

Miroslav Trpkovic
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
20 views1 page

Step 4: The GROUP BY Clause Is Applied To Partition The Rows Into Groups. at

The document discusses the GROUP BY clause in SQL queries. It explains that the GROUP BY clause partitions the candidate result set into groups based on the specified columns. Each group can initially contain multiple rows but will later be reduced to a single row. The example shows the candidate result set being grouped into six groups based on the values in columns t1.key_1 and t3.key_1.

Uploaded by

Miroslav Trpkovic
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 1

Chapter 3: Selecting 75

Now the candidate result set is much smaller:


t1. t1. t2. t2. t3. t3.
key_1 non_key_1 key_1 key_2 key_1 non_key_1 a b
===== ========= ===== ===== ===== ========= === ====
A 1 1 NULL NULL 3 333 100 3000
B 1 1 NULL NULL 4 333 100 4000
E 2 2 2 21 3 333 200 3000
F 2 2 2 22 3 333 200 3000
G 2 2 2 23 3 333 200 3000
H 2 2 2 21 4 333 200 4000
I 2 2 2 22 4 333 200 4000
J 2 2 2 23 4 333 200 4000
Step 4: The GROUP BY clause is applied to partition the rows into groups. At
this point each group consists of one or more rows; the reduction to
one-row-per-group happens much later. Also note that only grouping takes
place at this point; the actual GROUP BY ROLLUP ( t1.key_1, t3.key_1 )
clause is interpreted as GROUP BY t1.key_1, t3.key_1 and the ROLLUP pro-
cess happens later.
The GROUP BY clause may refer to both virtual columns and select list
items, but not to aggregate function calls or NUMBER(*) calls. However, if
there is a GROUP BY clause, the select list may only consist of aggregate func-
tion calls, NUMBER(*) calls, and items that appear in the GROUP BY clause.

Note: If a column appears in the select list, it must also appear in the
GROUP BY clause. However, the opposite is not necessarily true; if a column
appears in the GROUP BY clause it does not necessarily have to appear in the
select list. A GROUP BY clause often guarantees that the final result set will con-
tain no duplicate rows, but this is not necessarily the case if a column named in
the GROUP BY clause is omitted from the select list.

If there is no GROUP BY clause, each row in the candidate result set is treated
as a separate group.
In the end, each group will be reduced to a single row, but that cant happen
until later. Heres what the groups look like at this point, after the GROUP BY
t1.key_1, t3.key_1 process has finished:
t1. t1. t2. t2. t3. t3.
key_1 non_key_1 key_1 key_2 key_1 non_key_1 a b
===== ========= ===== ===== ===== ========= === ====
A 1 1 NULL NULL 3 333 100 3000 -- Group 1
-----------------------------------------------------------
B 1 1 NULL NULL 4 333 100 4000 -- Group 2
-----------------------------------------------------------
E 2 2 2 21 3 333 200 3000 -- Group 3
F 2 2 2 22 3 333 200 3000
G 2 2 2 23 3 333 200 3000
-----------------------------------------------------------
H 2 2 2 21 4 333 200 4000 -- Group 4
I 2 2 2 22 4 333 200 4000
J 2 2 2 23 4 333 200 4000

You might also like