General discussion

Locked

group by clause

By mehmud_kashif ·
please explain in detail who to use Group by clause in sql fro oracle 8

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

group by clause

by Bill_H In reply to group by clause

The GROUP BY clause allows you to calculate summary information from groups of rows that share a common value in one or more columns. Rows are combined using the GROUP BY clause.

When a GROUP BY clause is specified in a SQL SELECT command, Oracle places each row returned by a query into a group defined by the GROUP BY clause. Oracle then processes each group (usually by applying a group function specified in the SELECT clause), and returns a single row result for each group.

Groups defined by the GROUP BY clause may have one or more expressions. The general rule is, if the SELECT clause contains a combination of expressions, and at least one expression contains a group function, then the GROUP BY clause must contain all the expressions in the SELECT that *do not* contain a group function. Failure to GROUP BY a SELECT clause expression that does not contain a group function will result in an Oracle error.

If a column contains null values, those rows will be treated as one group in the query results.

HOW TO USE:

The GROUP BY clause follows the optional WHERE clause in a SQL SELECT command.

The following example demonstrates the use of group functions. All the rows in the TRAINING_SITE_ROOM table represent one group, in which the AVG, MIN, MAX, and SUM functions are applied. A single row result for the group is returned. The query result determines:
-Average classroom size for all training rooms (AVG).
-Smallest classroom size in the company (MIN).
-Largest classroom size in the company (MAX).
-Number of students that can be taught on any given day (SUM).

SQL Command:

SELECT
AVG(max_students),
MIN(max_students),
MAX(max_students),
SUM(max_students)
FROM training_site_room;

(continued in comment below...)

Collapse -

group by clause

by Bill_H In reply to group by clause

(Results)

AVG(MAX_STUDENTS) MIN(MAX_STUDENTS)
----------------- -----------------
12.75 9

MAX(MAX_STUDENTS) SUM(MAX_STUDENTS)
----------------- -----------------
18 153

The next example takes the previous query a step further. Instead of determining the classroom sizes at the company level, break it down to the training site level. (For each site, what is the average classroom size, the smallest and largest classrooms, and the maximum number of students for a given site?)

To do this, you need to create a "group" for each training site. For each group (training site), compute the average, minimum, maximum and summary class size. Create the training site group by SELECTing the training site (SITE_CODE), then specifying it in the GROUP BY clause.

SELECT
SITE_CODE,
AVG(max_students),
MIN(max_students),
MAX(max_students),
SUM(max_students)
FROM training_site_room
GROUP BY SITE CODE;

(Results)

SITE AVG(MAX_STUDENTS) MIN(MAX_STUDENTS)
---- ----------------- -----------------
DC 13.5 12
DNV 10.5 9
PDX 13.5 12
SEA 12.75 9

MAX(MAX_STUDENTS) SUM(MAX_STUDENTS)
----------------- -----------------
15 54
12 21
15 27
18 51

As stated in the answer, since the SITE_CODE field was specified in the SELECT clause, it *must* be included in the GROUP BY clause, or an error will be returned.

(more to come...)

Collapse -

group by clause

by Bill_H In reply to group by clause

This last example demonstrates the use of multiple fields in the GROUP BY clause. For each training site, display how many classes are scheduled to begin on the same day in the month of March 2001:

SELECT
site_code,
start_date,
COUNT(*)FROM class_schedule
WHERE start_date BETWEEN '01-Mar-2001'
AND '31-Mar-2001'
GROUP BY site_code, start_date;

(Results)

SITE START_DAT COUNT(*)
---- --------- --------
DC 05-Mar-01 1
DC 12-Mar-01 2
DNV 05-Mar-01 1
PDX 05-Mar-01 3
PDX 12-Mar-01 1
PDX 19-Mar-01 2
SEA 26-Mar-01 2

I notice that the TechRepublic system is not formatting the SQL commands and results the same way that I am entering them, so I will also send you an e-mail showing the commands and results in a more readable format.

Hope this helps...

Collapse -

group by clause

by Bill_H In reply to group by clause

Oops...small typo: in my first comment, the first GROUP BY clause example should read "GROUP BY SITE_CODE" (forgot the underscore...)

Back to Desktop Forum
4 total posts (Page 1 of 1)  

Hardware Forums