Group By
GROUP BY
GROUP BY allows users to apply other aggregate functions to sub-sets of the set of entities matching the query.
This is the fundamental building block for analyzing information by Type, Status, Priority etc.
Syntax
GROUP BY <FieldSelector> [AS "Alias"] { <AggregationFunction1>, <AggregationFunction2> ... } AS "Alias" |
The “FieldSelector” can currently be either a field name e.g.
GROUP BY Status { COUNT } |
Which would display results like this:
Or can be a function call:
GROUP BY Fill(Status) { COUNT } |
Which in this case would fill out the results to include all possible values (not just values included in the result of the query).
In the above GROUP BY statement, the function “Fill” will expand the GROUP BY to include all values from the Status picklist (which means the results will include 0-counts/0-sums).
Functions
Fill
The Fill function takes a single argument, the name of the field to GROUP BY. It will expand the results to include all groups, not just the groups which include 1 or more matches within the query.
Example:
GROUP BY Fill(Priority) { COUNT } |
Month
The Month function takes at least one argument, the name of the field to GROUP BY. The field must be a date/time field. It will group the results based on the Month of the date supplied (all results will be grouped by the first day of the month they belong to).
Optionally, it can also include a Format and/or Range function (see the Format and Range functions below).
Example:
GROUP BY Month(CreatedAt) { COUNT } |
This above aggregation would display results grouped by the first day of each month, as shown in this screen shot:
Day
The Day function takes at least one argument, the name of the field to GROUP BY. The field must be a date/time field. It will group the results based on the day of the date supplied (all results will be grouped by the day of the year they belong to, regardless of time). Optionally, it can also include a Format and/or Range function (see the Format and Range functions below).
Example:
GROUP BY Day(LastUpdatedAt) { COUNT } WHERE LastUpdatedAt >= DateSubtract(Now(), "2 weeks") |
Note: When executing group by queries, results will be confined to only those days included in the result set:
To return the empty days as well, across a date range, use the “Range” function (see below for more details):
Year
The Year function takes at least one argument, the name of the field to GROUP BY. The field must be a date/time field. It will group the results based on the year of the date supplied (all results will be grouped by the year they belong to). Optionally, it can also include a Format and/or Range function (see the Format and Range functions below).
Example:
GROUP BY Year(CreatedAt) { COUNT } |
Quarter
The Quarter function takes at least one argument, the name of the field to GROUP BY. The field must be a date/time field. It will group the results based on the quarter of the year they belong to. Optionally, it can also include a Format and/or Range function (see the Format and Range functions below).
Example:
GROUP BY Quarter(CreatedAt) { GROUP BY Project { COUNT }} |
Format
The Format function is a second-level function that can be passed to the various “Date” functions (Month, Day, Year & Quarter). It will be used to format the value of the date for display purposes – this allows you to convert the value in a more friendly format (so for example when grouping by month, you might want to output the date as “June – 2011”, instead of the default “2011-06-01”.
The format strings correspond to the Microsoft .Net Date/Time format strings, as documented here.
Note: The resulting string must be unique from all other group values strings, so for example if grouping by day, you could not provide a format string that only includes the year, as this would result in non-unique group names.
Example:
GROUP BY Year(CreatedAt, Format("yyyy")) { COUNT } WHERE EntityType = Requirement |
Range
By default the values will range from the natural start and end date of the results, but in some cases you may wish to extend the range further for the purposes of reporting (for example if you want to have the range include particular start and end dates).
Range takes 2 arguments, both date/time values, being the start and end dates of the range.
Example:
GROUP BY Day(LastUpdatedAt, Range('2011-1-1','2012-1-1')) { COUNT } WHERE Project = 'Project X' AND EntityType = Requirement |
Examples
GROUP BY packages to find when the first script assignment was created, and the last script assignment was updated (to see where activity in the project is occurring).
GROUP BY Package { Min(CreatedAt) AS "First Activity", Max(LastUpdatedAt) AS "Last Activity", COUNT AS "Total"} AS "Package" WHERE Project = "Project X" AND EntityType = ScriptAssignment |
A variation on the previous example, using the full path of the packages, as opposed to just the package name:
GROUP BY PackagePath { Min(CreatedAt) AS "First Activity", Max(LastUpdatedAt) AS "Last Activity", COUNT AS "Total"} AS "Package"WHERE Project = "Project X" AND EntityType = ScriptAssignment |
Here is how the output for the above query would look:
Find the number of entities being created per month over the last 3 years:
GROUP BY Month(CreatedAt, RANGE('2010-1-1', '2012-1-1'), Format('MMMM yyyy')) { COUNT } |
The above example will GROUP BY date with labels like “January 2010” etc.
Below is an example of nested GROUP BY aggregations – this example gives a break down of each incident, by component, then type and finally priority, where the status is “Open” or “Reopened”
GROUP BY Component { GROUP BY Type { GROUP BY Priority { COUNT } }} WHERE Status = Open |
The above query would return results like this, using nested tree nodes:
Note: Though we display the above query over multiple lines (for clarity), this is entirely optional and nested GROUP BY’s can all appear on a single line. Because pressing enter while typing executes the query, you need to hold the “SHIFT” key while pressing enter to move the cursor to the next line of the text editor.
