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

The “FieldSelector” can currently be either a field name e.g.

Which would display results like this:

Or can be a function call:

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:

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:

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:

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:

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:

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:

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:

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

A variation on the previous example, using the full path of the packages, as opposed to just the package name:

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:

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”

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.

Facet Functions

Facet Functions

Facets use functions to determine which values will be matched against each facet. A list of the available functions is below:

Note: some functions will not return true/false values, and those can only be composed together as values passed to other functions when constructing faceted queries.

Function: AndArgs

Syntax

Description

AndArgs will take two or more arguments, and return true, if all the arguments are also true.

AndArgs is useful to combine the results of two or more comparison functions together.

Examples

Where Status is Draft and Type is Bug:

Where Type is Epic and HasCoverage is true:

Yesterday()

Faceted LessThan(CreatedAt, Yesterday()) { COUNT } WHERE EntityType = Script

Faceted

Faceted

A faceted query allows you to specify groups/categories in which results will be matched and collected.

Overview

In a GROUP BY aggregation, results are grouped based on the value of a field within each result. In contrast the “Faceted” aggregation allows the user to provide the name and criteria for each group known as a “facet”.

Note: a single result can be collected into more than one group, where as a GROUP BY is guaranteed to collect each result into only one of the possible groups (values) for that field (except in the case of multi-select fields such as Components, Fixed/Affected versions etc.)

Facets should be familiar to anyone who has shopped online, where you will often see items divided in arbitrary groups which make it easer to find what you are looking for, i.e.

• $10 and less
• $10 to $50
• $50 or more
• On Special

Being able to break information down into meaningful ranges is a powerful tool which allows test managers to easily get an overview of the status of testing, and the quality/makeup of their test artifacts.

Examples

If you have a set of data with three distinct statuses:

• Draft
• Approved
• Out Of Date

You could get a count of each type by using this GROUP BY query:

But, what if you just want two counts, one for approved, and the other for both “Draft” and “Out of Date”?

This is where Faceted queries come in handy:

In the above query we are creating two “Facets” – the first facet for all matches where the Status is “Approved”, and the second which matches any status which is “Draft” or “Out Of Date”, and for each we calculate the count.

In the above example, we could also simplify it by counting everything that didn’t match the first facets predicate to be counted as the second facet, by using the Unmatched() function:

In the above example we are only showing two facets – but the number of facets you can define is unlimited – each facet just needs to be separated by a comma – and at the end of the list of facets you must define a set of aggregate functions to be executed for each facet, just as you would when writing a GROUP BY query.

Here is an example of the output you would expect from the above example:

Syntax

Faceted aggregations use the following syntax:

Each Facet is a function call with an optional alias (which must be provided in quotes):

If you do not supply an alias, the function call itself followed by the word “Facet” will be used as the name of this facet.

Functions

For a list of supported functions within a Facet, see the Facet Functions topic.

Entities

Entities

Overview

Aggregations are useful for collecting summary information across large sets of data, but in some cases you may wish to retrieve the names & ID’s of those entities which matched each group or facet. In these cases the “Entities” aggregate function can be used.

Syntax

Plain form:

Form with a set limit of results per node (if no value supplied, it will default to 50):

Form which will return any number of results:

Note: Be careful when using this option, it is not a good idea unless you absolutely need all the results, because results are not fetched in pages.

These queries may take a long time to execution when there is a large amount of data.

Examples

Group by the status of all entities in a particular project, displaying the entities matching each status:

Here is the output of executing the above query:

Note: Nodes can be clicked on to view the associated record, and hovering over the node will show the Type, Name and ID of that node.

Count

Count

Count is the simplest of aggregate functions, and will just return the count of matching entities.

Examples

Count all records in Enterprise Tester

Count all records in a project

Count all incidents in a project with a status of “Open”

Count all incidents, grouped by their status

AVG (Average)

AVG (Average)

Syntax

The AVG function can be used on any fields supporting aggregation which have a numeric type:

• TimeSpan
• DateTime
• Integer (Int32/Int64)
• Double
• Float
• Decimal

It will return the average value across all the values in the query (or the set of values being evaluated for the current Facet or Group).

Examples

This example would return the average number of steps for each script in a project.

This would return the average time taken to execute scripts within a project (which have an actual duration recorded).

This would return the average number of incidents raised per test script assignment, grouped by the type of the associated script.