Emojis - SQL in ICM InfoWorks - 12 Explicit Select Clauses and Group By Clauses 📊

 12 Explicit Select Clauses and Group By Clauses 📊

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

12.1 Explicit Select Clauses 📝 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.

• A SELECT sub-clause containing the expressions to be output to file or grids within the software. The SELECT sub-clause is compulsory. There can be one or more expressions separated by commas, these are termed ‘sub-expressions’ 📜
• A FROM sub-clause specifying the table from which the data should be extracted. This sub-clause is optional 📤
• An INTO sub-clause specifying the file into which the results should be output. This sub-clause is optional 📁
• A WHERE sub-clause specifying a condition which should be fulfilled before the object will be considered and included in the grid or file 🔍

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.

12.2 Group By Clauses 🧮 The 'Group By' clause consists of the following sub-clauses, most of which are optional.

• A SELECT sub-clause, as described above. As with the explicit select clause, this is compulsory 📜
• A FROM sub-clause, as described above. This sub-clause is optional 📤
• An INTO sub-clause, as described above. This sub-clause is optional 📁
• A WHERE sub-clause, as described above. This sub-clause is optional 🔍
• A GROUP BY sub-clause listing the fields, expressions, variables etc. used to group the objects. There can be one or more fields, expressions etc. separated by commas, these are termed ‘sub-expressions’ 📊
• A HAVING sub-clause specifying a condition for the group (rather than the object) which should be fulfilled for the data for this group to be output 🔍

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.code 📊

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

12.3 Implicit Group By Clause 📊 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:

• If there are no aggregate functions e.g. SELECT node_id,x,y then the query is clearly NOT an implicit GROUP BY ❌
• If there are aggregate functions acting on the results of aggregate functions e.g. SUM(COUNT(details.*)) then it clearly IS an implicit GROUP BY ✅
• If neither of the above apply then more detailed analysis has to be performed on each aggregate function as follows 🔬

If the aggregate function contains an array field or a one-to-many link followed by an asterisk e.g. SUM(details.), ANY(us_links.), then the aggregate function must be intended to work at an object level since this would not be valid at a group level.

If the aggregate function contains a field for an array field or a one-to-many link followed by an asterisk e.g. COUNT(details.code=’Z’), MAX(us_links.width<150), then the aggregate function must be intended to work at an object level since this would not be valid at a group level.