Sum of original estimate in issues by filter

I'd like to get a total sum of original estimate for issues (by filter condition) in a project that would do something like this:

SELECT SUM(originalEstimate) FROM ... WHERE project="Project Name" AND status IN("Resolved", "Closed");

Is something like this in any way possible from Jira? I managed to write a real SQL query to my MySQL database that gives me the result, but it's a bit clumsy compared to doing it from Jira.

12 answers

If you have ScriptRunner, this JQL query should show you the overall remaining workload for the current user:

 

project = PROJECT and assignee = currentuser() and issueFunction in aggregateExpression("Total Estimate for all Issues", "originalEstimate.sum()", "Remaining work", "remainingEstimate.sum()")

The documentation is here:

https://jamieechlin.atlassian.net/wiki/display/GRV/Scripted+JQL+Functions#ScriptedJQLFunctions-AggregateExpressions



Very Nice , But How do you display this in confluence page or any where else .

Hey,

we recently released a plugin that calculates totals... You might want to check it out:

https://marketplace.atlassian.com/plugins/com.atlassian.cps.sumUp

Cheers

Christian

It would have been great if SumUp included "Original Time Estimate" in the fields it can sum up :)

Great app for a great price. If only this was built in to JIRA already... Thank you for existing, sumUp.

When summing up hours is so central to Agile workflow, not having this in default JIRA does not make sense. The plugin is good, but extra payment feels weird.

Hi there,

founded built-in solution.

Its a workaround, but its working.

1) Create JQL filter returning issues you would like to sum

2) Create dashboard

3) Add "Workload Pie Chart" Gadget

4) Set it for that created filter & estimations you would like to see.

Works for me.

Hi,

 

When I create the pie chart like you said they only relevant option is the assignees but then it sums up the number of issues and not (ofcourse remaining).

Looking at the rest of options to choose grouping by the results, there is no effort estimation.

Can you advise?

Thanks!

Attaching screen of my settings.

Snímek obrazovky 2017-08-14 v 14.06.30.png

It solved my problem!

Thank you!!! :-)

Perfect solution. Thank you very much, Tomáš!

1 votes

JQL is not SQL. It is exclusively designed for finding issues, it has nothing to say about the output (the line blurs slightly with the "order by" but that's actually still separate from what JQL does)

Think of it as two separate parts. You do the search in JQL, and that gives you a list of matching issues. That's all it does.

If you want to see that output, you have to hand that filter to a display mechanism. Jira has loads of these - broadly the issue navigator, gadgets, reports. The display mechanism then reads and formats the data on the issues in whatever way you want.

So, there's no way to do this in a JQL query, because JQL tells Jira nothing about the output. You need to find a reporting mechanism. Have a look at the issue navigator and the "sum" fields (prefixed with sigmas in the "add column" drop down list). The project reports might tell you something, as well as some gadgets. I suspect none of them will do quite what you want, as they're all generalised, and you may need to write something.

Yes, I understand JQL != SQL, and knew it is not possible to do this with JQL, which is why I said "in any way possible from Jira" .. I might take a look in the Jira plugin database

Any luck finding a plugin to do this? It should be basic functionality on the new v6 rapidboard.

The same problem...

I need to receive sum of Remeining time for all tasks assigned to currentUser()

:(

Would also love to have a SUM function in JQL.

It would be nice if this was default or a plugin was added. The way I do this right now is to add the orig estimate column in the issue navigator and move it over near the start. That way a tot up can be done easily enough, e.g:

I would also like to find an answer to this. Currently I'm being forced to export into excel to do this..... and I HATE spreadsheets.

A standard "Provide Column totals" tickbox on the display gadget would be perfect fo this.

I can see this on the main dashboard as a option for adding a field, yet I can't write a filter showing the Sum of values. I'd love to say where sum(timeoriginalestimate) is 0, highlight these issues in my filter. I'm using with sub-tasks where the hours are actually estimated.

Any thoughts on how to fix would be ideal!

This is a high item for me as well. I use subtasks, and being able to tell if subtasks exist or if they have any aggregate time allocation is important to understand work in sprint not yet accounted for.

Any news on this? It is unbelievable that a plug-in to sum estimates per user in a release does not exist!

I have the same problem. How can I add these values and keep them "available" to make some calculations that I want to put in some reports?

Besides, the info I want to add is the total time spent by the issue, since its beginning until its closure.

As I have seen so far, the sumUp add-on adds only numeric fields.

Any help, please?

Hello,

Is there any update on this? A plug in which sums the original/actual estimates?

yeah, this is really annoying, and something you would expect by default. Rally has a nice way to total while planning. Also, the solution suggested, I don't have any sort of gadget called "Workload Pie Chart" - it's not 'built in'. So... 

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

2,754 views 11 18
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot