Sum of custom field from all the linked child stories to a field in parent story using SQL for JIRA

Consider below scenario -

I have field in story - "Field_1"

I have created link - Outward Description > is parent of

Inward Description > is child of

 

Now,

I want to sum up the values from all the child stories to parent story.

(We can have a JIRA Value field configured to carry the sum of the values. Let that field be FUNCTIONAL POINT)

 

Eg- 

I have a parent story AB-123

I have three child stories linked to AB-123, let that be AB-124, AB-125, and AB126.

 

I want to sum the value of Field_1 of all the child stories to FUNCTIONAL POINT field of the parent story.

 

I have been successful in getting the below query -

SELECT SUM(FUNCTIONAL_POINT) FROM (SELECT ISSUECUSTOMFIELDS.ISSUEID, ISSUECUSTOMFIELDS.FUNCTIONAL_POINT FROM ISSUECUSTOMFIELDS WHERE ISSUECUSTOMFIELDS.ISSUEID IN (SELECT ID FROM ISSUES WHERE ID in (SELECT CHILDISSUEID FROM ISSUELINKS WHERE TYPE='Parent - Child' and ISSUEID = '33905')))

Now, This query is static for a particular issue id. If this can be made dynamic it would be helpful.

JIRA Db supported query for the same is -

SELECT SUM(NUMBERvALUE) FROM customfieldvalue cv WHERE cv.ISSUE IN
(SELECT ji.ID FROM JIRAISSUE ji WHERE ji.ID IN
(SELECT il.Destination FROM ISSUELINK il WHERE il.LINKTYPE = 10400 AND il.ID='10801'))

0 answers

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published May 30, 2018 in Marketplace Apps

Three tips for boosting your board's efficiency with Story Maps

Trello is one of the most effective tools for driving your sprints. It's customizable for every Agile team and product owners and Scrum masters (SM) love it. However, Agile teams often struggle with:...

849 views 2 9
Read article

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