Virtual Tables, External Data, and Aggregates
Page 6 of 6

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

  1. 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

  2. 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

  1. 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

  2. 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.

  1. Open a command window and change directories to your_tutorial_home.
  2. Run your setclasspath script.
  3. Open JBMSTours.GenerateReport.java (in /demo/programs/tours) and look at the queries it runs.
  4. Run the program, specifying the system directory with the -D parameter:

    java -Dcloudscape.system.home= your_tutorial_system JBMSTours.GenerateReport

  5. Examine the output.