Database Guide

Using a GROUP BY clause

The query in the following example selects all rows in the STAFF table of the SAMPLE database and groups them by job type. The first block of code issues the SQL statement directly in the code. The second block retrieves the SQL statement from an access set. To look at the results of this query, follow these steps:

  1. Evaluate the code using the Inspect command.
  2. Look at the contents of the ordered collection in the Inspector window.
"GROUP BY Clause"
| querySpec result resultCollection connection |
resultCollection := OrderedCollection new.
connection := AbtDbmSystem activeDatabaseConnectionWithAlias: 'SAMPLE'.
querySpec := (AbtQuerySpec new)
     statement: 'SELECT STAFF.NAME, STAFF.ID, STAFF.DEPT,
                     STAFF.JOB, STAFF.YEARS, STAFF.SALARY,
                     STAFF.COMM FROM STAFF GROUP BY STAFF.JOB,
                     STAFF.DEPT, STAFF.NAME, STAFF.ID, STAFF.YEARS,
                     STAFF.SALARY, STAFF.COMM'.
result := connection resultTableFromQuerySpec: querySpec.
result do: [:eachRow | resultCollection add: (eachRow asString)].
^resultCollection.
 
"GROUP BY Clause using an access set"
| result resultCollection connection |
resultCollection := OrderedCollection new.
connection := AbtDbmSystem activeDatabaseConnectionWithAlias: 'SAMPLE'.
result := connection resultTableFromQuerySpec:
     (AccessSetName getQuerySpecNamed: #GroupBy).
result do: [:eachRow | resultCollection add: (eachRow asString)].
^resultCollection.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]