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
Community showcase
Published Aug 22, 2018 in Marketplace Apps

How a Marketplace app tech team is achieving gender diversity

Hello! My name is Genevieve Blanch, and I'm the Marketing Manager at RefinedWiki, creators of apps to give teams the tools to customize Atlassian platforms. Currently, 44% of the tech team at Re...

540 views 3 19
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