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

Multi tool use
Multi tool use












0















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.










share|improve this question



























    0















    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.










    share|improve this question

























      0












      0








      0








      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.










      share|improve this 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.







      azure-devops wiql






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Jan 2 at 15:36









      R PelzerR Pelzer

      693826




      693826
























          1 Answer
          1






          active

          oldest

          votes


















          1















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




          Based on my experience, the short answer is no.



          As you mentioned that GroupBy and Count is not supported currently. For more information, we could get the WIQL syntax from this document.



          Azure Devops also supports use the WIQL Api to query the result. My workaround is that you could query the result as a list then we could groupby and count the result with our customized code.



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


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






          share|improve this answer























            Your Answer






            StackExchange.ifUsing("editor", function () {
            StackExchange.using("externalEditor", function () {
            StackExchange.using("snippets", function () {
            StackExchange.snippets.init();
            });
            });
            }, "code-snippets");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "1"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            autoActivateHeartbeat: false,
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009091%2fusing-a-group-by-and-count-statement-in-wiql-azure-dev-ops%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes









            1















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




            Based on my experience, the short answer is no.



            As you mentioned that GroupBy and Count is not supported currently. For more information, we could get the WIQL syntax from this document.



            Azure Devops also supports use the WIQL Api to query the result. My workaround is that you could query the result as a list then we could groupby and count the result with our customized code.



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


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






            share|improve this 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 that GroupBy and Count is not supported currently. For more information, we could get the WIQL syntax from this document.



              Azure Devops also supports use the WIQL Api to query the result. My workaround is that you could query the result as a list then we could groupby and count the result with our customized code.



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


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






              share|improve this answer


























                1












                1








                1








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




                Based on my experience, the short answer is no.



                As you mentioned that GroupBy and Count is not supported currently. For more information, we could get the WIQL syntax from this document.



                Azure Devops also supports use the WIQL Api to query the result. My workaround is that you could query the result as a list then we could groupby and count the result with our customized code.



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


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






                share|improve this answer














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




                Based on my experience, the short answer is no.



                As you mentioned that GroupBy and Count is not supported currently. For more information, we could get the WIQL syntax from this document.



                Azure Devops also supports use the WIQL Api to query the result. My workaround is that you could query the result as a list then we could groupby and count the result with our customized code.



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


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







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Jan 3 at 3:13









                Tom SunTom Sun

                17.6k2923




                17.6k2923
































                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Stack Overflow!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54009091%2fusing-a-group-by-and-count-statement-in-wiql-azure-dev-ops%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    5VNkGgPtentt6lGAYj39WhbwKW,ca2DAq,Pf65 wE6 AKrirDMGtPCwkDB1CR9dOkq,0ENg,9N
                    it,J0y8QLM,Z5sMC4I9cuyKLF fRKUm5noAYTFJS,FIHft3sDloEAI1hKX

                    Popular posts from this blog

                    Monofisismo

                    Angular Downloading a file using contenturl with Basic Authentication

                    Olmecas