You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.
You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview
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 set 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 of Specifying Set Functions
SQL Reference Manual, GROUP Clause
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, HAVING Clause
More examples for Data Query