The purpose of an explicit select clause is to generate a grid or CSV file of values with one row in the grid or file for each object meeting given criteria.
'Group by' clauses, on the other hand, allow you to calculate totals, averages, maxima, minima etc. for groups of objects e.g. by system type, length, diameter. The objects can be grouped by one or more fields, variables, expressions or some combination of these. These can also be displayed in grids or output to CSV files.
It is also possible to store the results of a 'group by' clause in a 'table variable' with the aim of then using those results in a further select clause known as an implicit join.
As described later in this topic, it is possible to perform an implicit GROUP BY in which the totals, averages etc. are calculated for all objects in the table meeting given criteria. These clauses look very similar to explicit select clauses. The means by which the two are distinguished will be described below.
An explicit select clause may be used to generate grids or CSV files containing one or more values for each object meeting given criteria.
The main advantage of using an explicit select clause over other ways of displaying data in the software is that you can display the results of calculations, including calculations on one-to-many links and array fields.
An explicit select clause consists of the following sub-clauses, most of which are optional.
If no FROM sub-clause is specified, the current table will be used.
If no INTO sub-clause is specified, the results will be displayed in a grid.
If no WHERE sub-clause is specified, all the relevant objects will be considered.
The keyword DISTINCT can be used with SELECT to select a group of values without duplicates e.g.
SELECT DISTINCT system_type
will display a grid of each system type in the table.
DISTINCT can be used in conjunction with WHERE and ORDER BY, but not GROUP BY, HAVING, TOP or BOTTOM.
Where multiple fields are selected, the query returns one line for each distinct set of values, rather than the distinct values for each field.
SELECT distinct width,ds_width WHERE pipe_material = 'co' ORDER BY width ASC,ds_width ASCThe 'Group By' clause consists of the following sub-clauses, most of which are optional.
If no FROM sub-clause is specified, the current table will be used.
If no INTO sub-clause is specified, the results will be output to a grid within the software.
If no WHERE sub-clause is specified, all the objects will be considered.
If no GROUP BY sub-clause is specified, all the objects in the table will be considered as one group (this is known as an ‘implicit GROUP BY’)
If no HAVING sub-clause is specified, all the groups will be output to the file or grid.
It is possible to use a blob field in a GROUP BY query e.g.
SELECT SUM(COUNT(details.*)) GROUP BY details.codeWhen the result of the GROUP BY clause is output to a file, the values for all the sub-expressions in the GROUP BY sub-clause and the SELECT sub-clause will be output to the file. The records output will be sorted in an order determined by the sub-expressions in the GROUP BY clause.
It is possible to alter the text used in the header of the CSV file or the grid for each expression within the SELECT sub-clause by using the keyword AS e.g.
SELECT COUNT(*) AS MyCount, MAX(x) AS MaxX Group BY status
Note that the aliases are not strings, they do not appear in quotation marks. If you want to have spaces in the names you should deal with them as with variables with spaces in the name and use square brackets e.g. [My Title].
The aggregate functions described above, with the exception of FIRST and LAST may be used in the sub-expressions in the SELECT sub-clause. It is also possible to use the special sub-expression COUNT(*), which will return the number of objects in each group.
It is possible to write a query which works like a GROUP BY clause but instead of grouping things by one or more fields or expressions, considers all the objects at once. A simple example of this would be a query that counts the number of objects in the table e.g. SELECT COUNT(*)
This is different from an explicit select clause e.g. SELECT node_id
The rule for determining whether a query is an implicit group by or a select acting on individual objects is as follows: