[top]
[prev]
[next]

Road Map
Glossary
Table of Contents
Index
Documentation Top
Global Index
|
GROUP BY and GenerateReport
SQL allows you to see aggregates for subsets of data. It provides GROUP BY for this.You combine GROUP BY with any aggregate. When combined with the HAVING clause, it allows you to see aggregates for any subsets that meet an aggregate-related search condition.
Use GROUP BY
- Find the hottest city in each region. You will have to join the Cities and Countries tables.
SELECT MAX(city.showTemperature()), Region FROM Cities JOIN Countries ON Countries.country_ISO_code = Cities.country_ISO_code GROUP BY Region
- Now find the second hottest city in each region. You will have to join the Cities and Countries tables.
SELECT MAXBUTONE(city.showTemperature()), Region FROM Cities JOIN Countries ON Countries.country_ISO_code = Cities.country_ISO_code GROUP BY Region
Use HAVING
- Find the hottest city in each region, but show only those regions that have an average temperature of at least 70 degrees:
SELECT MAX(city.showTemperature()), Region FROM Cities JOIN Countries ON Countries.country_ISO_code = Cities.country_ISO_code GROUP BY Region HAVING AVG(city.showTemperature()) > 70.0
Quiz: What's wrong with the following query?
SELECT SUM(rooms_taken) FROM HotelAvailability GROUP BY booking_date HAVING rooms_taken > 3
|
Answer: HAVING should operate on an aggregate-related expression or a grouping column in conjunction with a GROUP BY, not on a column expression. The query can be rewritten to use a WHERE clause instead:
SELECT booking_date, SUM(rooms_taken) FROM HotelAvailability WHERE rooms_taken > 3 GROUP BY booking_date
Here's a similar query with a correct use of the HAVING clause:
SELECT booking_date, SUM(rooms_taken) FROM HotelAvailability GROUP BY booking_date HAVING AVG(rooms_taken) > 3
|
- Exit ij and shut down the system with the exit command:
exit;
Run JBMSTours.GenerateReport
GenerateReport is an application that uses aggregates like the ones in the previous examples to generate a "report" about the toursDB repository data and about JBMSTours customers.
- Open a command window and change directories to your_tutorial_home.
- Run your setclasspath script.
- Open JBMSTours.GenerateReport.java (in /demo/programs/tours) and look at the queries it runs.
- Run the program, specifying the system directory with the -D parameter:
java -Dcloudscape.system.home= your_tutorial_system JBMSTours.GenerateReport - Examine the output.
|