Entering content frame

This graphic is explained in the accompanying text Creating Groups: GROUP BY, HAVING Locate the document in the library structure

Prerequisites

You require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

Grouping of Values: GROUP BY

You can use the GROUP clause to group together the values in a given column and, if necessary, to perform additional calculations for each of these groups using the different column values. With the exception of the column to be grouped, you have to specify a Structure linkset function for each additional column. You have to specify the GROUP clause after the FROM and WHERE clauses.

 

SELECT type, SUM(free)
  FROM hotel.room
    GROUP BY type

Number of rooms free, grouped by room type

The set function used here is the SUM function that calculates the sum of the values.

Result

Since the second column in the results table does not contain any predefined names, the database system selects the column name EXPRESSION1.

TYPE

EXPRESSION1

Double

1088

Single

376

Suite

336

 

SELECT type, MIN(price) min_price, FIXED (AVG(price),5,2) avg_price, MAX(price) max_price
  FROM hotel.room
    GROUP BY type

Calculating the price categories for the rooms, grouped by room type

The set functions used here are MIN (minimum value), AVG (average value), and MAX (maximum value).

Result

The columns in the results table are assigned the names MIN_PRICE, AVG_PRICE, and MAX_PRICE.

TYPE

MIN_PRICE

AVG_PRICE

MAX_PRICE

Double

80

160.67

270

Single

45

96.67

160

Suite

300

475

700

 

See also:

Additional Examples for Specifying Set Functions

SQL Reference Manual, Structure linkGROUP Clause

 

Groups with Conditions: HAVING

You can use the GROUP clause to group together the values in a given column. You can then use the HAVING clause to exclude groups from the temporary results table. In contrast to using the WHERE clause for excluding values, a set function that refers to an entire column in a temporary results table usually precedes the HAVING clause.

 

SELECT type, MIN(price) min_price, FIXED(AVG(price),5,2) avg_price, MAX(price) max_price
  FROM hotel.room
    GROUP BY type
    HAVING MIN(price) >= 70

Calculating the price categories for the rooms, grouped by room type; displaying only the room types with rooms at a price of at least 80 euro

Result

TYPE

MIN_PRICE

AVG_PRICE

MAX_PRICE

Double

80

160.67

270

Suite

300

475

700

 

See also:

SQL Reference Manual, Structure linkHAVING Clause

More examples for Data Query

 

Leaving content frame