Using a Group By and Count statement in WIQL (Azure Dev Ops)

Question

I want to make a WIQL query to count the workitems from the type 'Bug', grouped by the title and the software version fields. But, there is no group by function in WIQL. Does anyone have an alternative to make this work in WIQL?

SELECT
    [System.Title],
    [System.State],
    count(*)
FROM workitems
WHERE
    [System.WorkItemType] = 'Bug'
    AND [System.State] <> ''
GROUP BY [System.Title], [Custom.ToolSoftwareVersion]

The queries are used to create dashboards and overviews, ect.

Answer 1

Does anyone have an alternative to make this work in WIQL?

Based on my experience, the short answer is no.

As you mentioned, GROUP BY and COUNT are not supported currently. More information on the WIQL syntax is available in this document.

Azure DevOps also supports using the WIQL API to query the result. My workaround is that you could query the result as a list, then you could GROUP BY and COUNT the results with our customized code.

POST https://dev.azure.com/{organization}/{project}/{team}/_apis/wit/wiql?api-version=5.0-preview.2

By the way, you also could give your feedback to the Azure DevOps team.

Answer 2

An alternative approach is to use a "Chart for Work Items" widget that provides a Pivot Table view. You can choose aggregation types such as COUNT, SUM.

This operates similarly to Pivot Tables in Excel with numbers in the output rather than a chart.

1. Choose Chart Type:

2. Choose row and column axes

3. The result is presented as a table with "group by" / count style