jira cloud query to sum all story points by sprint and component

Hi Sara,

You can use JQL to retrieve all the issues matching the sprint and component, for example:

Sprint = 1234 AND component = "FE"

However, JQL itself cannot perform aggregations such as SUM on Story Points. It only returns the issues matching the query.

If your goal is to calculate the total Story Points, you have a few options:

• Use Jira’s native reporting features.
• Use Jira Automation to retrieve the issues with JQL and sum the Story Points using smart values.
• Use the Jira REST API and calculate the total externally.
• Use a Marketplace reporting app if you need this kind of aggregation regularly.

For example, with Automation you could use Lookup issues with:

Sprint = 1234 AND component = "FE"

and then access the total with:

{{lookupIssues.Story Points.sum}}

Just make sure to use the correct Story Points field for your Jira configuration, as this can differ depending on the project setup.

If you have any trouble setting it up or this doesn’t quite cover your use case, feel free to reach out to me by email and I’ll be happy to take a closer look with you.

Hope this helps!

Hi @Sara Salem_ Vodafone 

Adding to the automation suggestions above: if {{lookupIssues.Story Points.sum}} comes back empty, check which field your instance actually uses. Many instances have more than one story points field, and team-managed projects use a separate "Story point estimate" field. Referencing the field by ID avoids the problem, e.g. {{lookupIssues.customfield_10016.sum}}.

If you want the number on a dashboard instead, native gadgets won't help — Issue Statistics and Two Dimensional Filter Statistics only count issues, they can't sum a number field. That part needs an app. Disclaimer: I work for Plugio, the vendor of Plugio Suite. It can show Story Points summed for that JQL, or broken down by component. Its sprint mode also follows a board's active sprint, so you don't have to update the sprint ID every sprint.

Hi @Sara Salem_ Vodafone,

You cannot sum-up fields via JQL. With a JQL like this you can filter the issues from that sprint that have a certain component:

Sprint = 1234 AND component = "FE"

For summing-up the story points field of these issues, you could export them in CSV and use Excel to calculate the SUM. 

However, such a filter will not return the subtasks from the sprint, in case of a team managed project, due to a Jira JQL bug/limitation (JRACLOUD-81069 ).

An alternative would be to search for an app/plugin on Atlassian Marketplace that offers capabilities such. 

If you are willing to trying an app, our Great Gadgets offers some nice dashboard gadgets that you could use to achieve this with just a few clicks.

For your use case, I would recommend using the Pivot Table & Pivot Chart gadget, which works just like pivot table from Excel. In can easily calculate the sum of Story Points split by Components. 

To get this, the gadget should be configured like this:

You can configure the gadget in many other ways:

• Charts of various types (pie, bar, line, etc) for better visualization.

• Tables or heatmap tables split by multiple fields for more detailed statistics

You can also use its Issue Filter Formula or Advanced Issue Filter Formula gadgets to display sum of Story Points per Component as a number. 

Hope this helps.

Danut.

Hi @Sara Salem_ Vodafone,

JQL is a filter language, so it returns the matching work items but never a computed total. sum is even a reserved word in JQL, so the aggregation can't be expressed in the query itself.

Two things worth adding to the JQL you'll use as the basis for any approach:

• Scope it to a project. component is a project-level field, so a query without a project clause can also match same-named components in other projects. Use project = ABC AND sprint = 1234 AND component = FE, and quote any value containing spaces.
• Avoid hardcoding the sprint ID. sprint in openSprints() matches the sprint that is started but not yet completed, so the same filter keeps working next sprint: project = ABC AND sprint in openSprints() AND component = FE.

For the total itself, a scheduled automation rule is the most practical native route:

1. Trigger: Scheduled (for example daily, or right before your review).
2. Action: Lookup work items, with the JQL above.
3. Then either log {{lookupIssues.Story Points.sum}}, write it into a number field on a summary work item, or send it out by email.

Referencing the field by ID, as in {{lookupIssues.customfield_10016.sum}}, tends to be more reliable, since many instances carry both a "Story Points" and a "Story point estimate" field and only one of them holds your values.

Best,

Ivan

Hey @Sara Salem_ Vodafone ,

Where would you like to store this information? For example, you could probably build automation to do so, but you would then either have to store it on a specific work item, send it as information in a messaging tool or email, or fetch it somehow. 👀

On the other hand, if you have Confluence, you could create new database, import work items from Jira by using something like:

project = ABC and spring = 1234 and component = FE

Then add Story Points as a column to a database and use "Sum" calculation at the bottom.

The only thing is that it's not dynamic, so you would need to manually refresh it each time you add, remove, or update work items or story points. 🫤

Hope this helps.

Cheers,
Tobi

Hi @Javier Martínez

Welcome to the Atlassian Community!

Jira's native JQL can filter the work items, but it cannot calculate a SUM.
Your JQL would simply be -- sprint = 1234 AND component = FE
If you only need to see the total Story Points for that sprint/component, one simple native option is to save this filter and use Jira reporting/dashboard features to visualize the result.

If you need the total for further processing, Jira Automation is a good option.
Use Lookup work items with the JQL above and then sum the Story Points using a smart value such as
{{lookupIssues.Story Points.sum}}

Regards,
Gor