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

[Swapnil Tiwari]

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'))