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
Atlassian Community Anniversary

Happy Anniversary, Atlassian Community!

This community is celebrating its one-year anniversary and Atlassian co-founder Mike Cannon-Brookes has all the feels.

Read more
Community showcase
Bridget Sauer
Published 11 hours ago in Marketplace Apps

Calling all developers––You're invited to Atlas Camp 2018

 Atlas Camp   is our developer event which will take place in Barcelona, Spain  from the 6th -7th of   September . This is a great opportunity to meet other developers and get n...

35 views 0 3
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