GROUP BY and PIVOT BY

Not all NSQL statements will produce a Domain output. For example, to determine the number of customers of each sex in the database. The statement:

Here we are requesting a count of all records in the customer table, grouped by the Sex column, this NSQL statement produces a one dimensional crosstab, the sheet view of which appears as:

The customer table contains 5 females, 5 males and 1 record with a Null value for Sex. Such statements can also include a WHERE clause, to determine which records are to be included in the aggregation:

Will return:

In addition to counting the SELECT clause can be used to specify an aggregation of a numeric field. To determine the average age of customers of each distinct sex, the statement:

Will return:

And

Will return:

Additionally the functions MAX and MIN are available and when used in the previous statement will return the maximum or minimum age for each gender in the customer table.

The WHERE clause can, of course, be applied to restrict the records that are to be included in the calculation, for example:

Will return:

A second dimension, or axis, can be added to the result set by applying the PIVOT BY clause after the GROUP BY clause:

This statement will produce:

As before, functions of numeric fields can be substituted for counting and the WHERE clause can be used to select the records that should form part of the crosstabulation.

The PIVOT BY clause can only be included after a GROUP BY clause, and each can only have one column. The SELECT clause can be followed by count (*) or a function of one column.

The only functions against which GROUP BY is allowed are:

Count, Sum, Mean, Avg, Min, Max, StDev and StDevs.

• NSQL Basics
  • Structure
  • SELECT Statement
  • WHERE Clause
  • Additional Functions
  • GROUP BY and PIVOT BY
  • Operators
  • Wildcard Operators
  • Aggregate Functions