How to perform JQL queries similar to sum and Group By in SQL

David Allen May 25, 2020

I have just being using Jira for a couple of months.  I have use cases where I want to report total hours as opposed to a list of issues e.g. Total Time remaining in a particular status for all Tasks in a particular search criteria.  JQL can easily get me to the point of returning the list of issues that match the search criteria, but I can see no way of totaling withing Jira.   In SQL speak it would be something like, 

select Status, sum (sigma Original Estimate) as TotalTimes

from Issues

Group by Status

 

this would return something like:

Status               TotalTimes

Done                250

In Progress       300

To Do                110

If JQuery is the wrong way to proceed, can someone suggest another way of generating such result sets.  As a bounus I would like to be able to expose these on widgets - any guidance in this regar would be welcome

 

2 answers

0 votes
Tim Wright
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
October 23, 2024

Only way I have done this is with  (paid for)add ins on bar charts., which is annoying as this functionality comes as standard in ServiceNow

0 votes
John Funk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 25, 2020

Hi David - Welcome to the Atlassian Community!

I will admit upfront that I have not try to do any summing of hours before, but you might be able to get what you want or get close using a Dashboard and gadget.

If the filter is returning what you want, then create a Dashboard and use the Two Dimensional Issue gadget. Then use Status and Time Spent as the fields. 

I hope that helps!

David Allen May 28, 2020

Hi John - thanks

I tried as you suggested, even though my query returns the Time Spent field, this field is not available for selection in the XAxis and YAxis dropdowns in the Two Dimensional Filter Statistics  gadget.

John Funk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 29, 2020

Well, that stinks. Not sure why it is limited like that. 

Maybe try the Issue Statistics gadget?

Nic Brough -Adaptavist-
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
May 30, 2020

The 2d-filter gadget can only work with things indexed from a fixed list.  Time-spent is not a fixed list with a possible human-friendly number of options (we struggle with lists over 35-40).  It could be 1 minute -> any integer number of minutes -> the maximum integer yout database can support.

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
SERVER
TAGS
AUG Leaders

Atlassian Community Events