how to sub-total or group by in advanced query

[Rose Abbl]

hello,

writing a query and want to sub-total by a field with the end goal to get sum total of points by this field.  can someone help?

thanks so much

[Jack Brickey · Accepted solution]

Hi Rose, you can't illustrate "sum" within the JQL capabilities. There are possible solutions leveraging Automation and custom fields in some scenarios. you might consider exporting to Excel or some addons, like EasyBI.

[Roberts Čāčus]

Hi @Rose Abbl 

Suppose you have eazyBI installed in your Jira or want to explore its options with a free trial. In that case, you can return the sum of Story Points from issues with specific custom field values. You can see one such example with a sub-total of Story Points for issues under specific Epic Links - https://eazybi.com/accounts/1000/cubes/Issues/reports/166439-story-points-progress-and-velocity-by-sprints-in-epic.

You can replace Epic Links with the desired custom field dimension.

Thank you @Jack Brickey for suggesting eazyBI.

Best,

Roberts // support@eazybi.com

[Rose Abbl]

thank you @Jack Brickey for your response.  it isn't quite what I'm looking for.  sorry i was not clear.  Each epic includes stories that fall into 4 categories (team skillsets).  We are using Labels to indicate these categories. Skillset1, skillset2, etc.  I am trying to get points/label.  Jira currently has a pie chart widget for # of stories/label.  I want to take this another step and get #of story points/label.  

project = <x> AND Sprint in openSprints() AND type != Sub-task ORDER BY labels 

can I sub-total story points in this query by label?

[Jack Brickey]

i am not familiar with stories/label gadget, please share more on this.

Personally I would move away from labels. for this UC you need a single select field so you can control the selection. Labels can include more than one option and even misspellings.

[Rose Abbl]

thanks @Jack Brickey . do you have a recommendation for a Jira field to use to customize to single select?  

even if we used a different field, how can I group and sub-total by a single-select field?

[Jack Brickey]

Jira provides only one "single-select" custom field. You would create a new custom field of this type and add in all of the values that you want to be able to choose from.

[Rose Abbl]

you missed the 2nd part of the question :)  even if we used a different field, how can I group and sub-total by a single-select field?  it has been my experience JQL does not support Group By.  

[Jack Brickey]

well that is where the messy bit comes into play. You could export to excel and use it group and sum. If you must do in Jira then maybe use Parent-Child solution then automation to update the Parent (sum of all parts) ever time the child value is updated.

in short there isnt a clean solution to your goal OOTB.

[Rose Abbl]

thanks for all the feedback.  appreciate it.

[Anthony Kozlov_SaaSJet_]

Hi @Rose Abbl .

As an alternative, try a Time in Status for Jira Cloud add-on developed by my SaaSJet team. It supports a Pivot Table View, which can make a sub-total by a field with the end goal to get a sum total of points by this field.

Monitor time for any issue fields, any combination of issue attributes (e.g: assignee time per status, time in status per assignee, compare status time for the whole team).

Manipulate the data by math functions (Average, Min/Max, Median, Count, Sum, etc)

You can check the trial version or order a demo call.  Hope it helps.

[Rahul_RVS]

Hi @Rose Abbl 

Welcome to the community !!

To visualize the issue hierarchy and sum up of numeric fields / story points / time estimates at each hierarchy level, we have created an app

Agile Tools - Epic Tree, Links Tree and Time in Status 

You can also view the full Jira Issue Type hierarchy of Linked issues, Portfolio/Advanced Roadmaps, Epics, and Subtasks up to 10 levels.

Key features :

• Hierarchy upto 10 levels deep
• Progress % on "remaining estimate" or "original estimates"
• Edit Issue summary, time estimates, story points and assignee on the tree with real time updates in the progress
• Rolled up percentage completion at all levels
• Ability to add/remove the columns on the report

Along with this there are multiple other features like Time in Status, Worklogs reports etc which the app provides.

Disclaimer: I work for RVS, the vendor for this app, and I am one of the developers of the app

[Rose Abbl]

thank you @Rahul_RVS 

for your response.  I'm looking to get story points sub-totaled by Labels.  can you see my response above?

[Rahul_RVS]

Hi @Rose Abbl 

We have a JQL search option as well within the app, wherein you can filter stories of a specific label and the app will show sum up of all issues below those stories.

Hope this helps.

Thanks

Rahul