XSLT 2.0: Group-By and Sum Issue

Question

Here is my source XML

<?xml version="1.0" encoding="UTF-8"?>
<Workers>
    <Worker>
        <empID>12354</empID>
        <Cost>
            <Date>2023-10-06-07:00</Date>
            <Type>Expense</Type>
            <Amount>108</Amount>
        </Cost>
        <Cost>
            <Date>2023-10-06-07:00</Date>
            <Type>Bonus</Type>
            <Amount>200</Amount>
        </Cost>
        <Cost>
            <Date>2023-10-06-07:00</Date>
            <Type>Expense</Type>
            <Amount>550</Amount>
        </Cost>
        <Cost>
            <Date>2023-10-10-07:00</Date>
            <Type>Expense</Type>
            <Amount>100</Amount>
        </Cost>
        <Cost>
            <Date>2023-10-10-07:00</Date>
            <Type>Bonus</Type>
            <Amount>200</Amount>
        </Cost>
        <Cost>
            <Date>2023-10-10-07:00</Date>
            <Type>Expense</Type>
            <Amount>120</Amount>
        </Cost>
        <Cost>
            <Date>2023-10-11-07:00</Date>
            <Type>Expense</Type>
            <Amount>120</Amount>
        </Cost>
    </Worker>
    <Worker>
        <empID>4567</empID>
        <Cost>
            <Date>2023-10-06-07:00</Date>
            <Type>Expense</Type>
            <Amount>90</Amount>
        </Cost>
        <Cost>
            <Date>2023-10-06-07:00</Date>
            <Type>Bonus</Type>
            <Amount>10</Amount>
        </Cost>
        <Cost>
            <Date>2023-10-06-07:00</Date>
            <Type>Expense</Type>
            <Amount>120</Amount>
        </Cost>
        <Cost>
            <Date>2023-10-10-07:00</Date>
            <Type>Expense</Type>
            <Amount>1297</Amount>
        </Cost>
        <Cost>
            <Date>2023-10-10-07:00</Date>
            <Type>Bonus</Type>
            <Amount>312</Amount>
        </Cost>
    </Worker>
</Workers>

Expected output

• Group the record based on <empID> and <Date>
• Create as many Worker nodes as equal to unique <Date>values so I have three unique <Date> values under 1st <Worker> node that has <empID> value as 12354. My expected output XML needs to contain three <Worker> nodes for <empID> 12354 because there are three unique values such as
• 2023-10-06-07:00
• 2023-10-10-07:00
• 2023-10-11-07:00

and two <Worker> nodes for <empID> 4567 because there are two unique values such as

• 2023-10-06-07:00

• 2023-10-10-07:00

• Sum the amount for Type of Expense for a unique <Date>

• Sum the amount for Type of Bonus for a unique <Date>

Expected Result

<?xml version="1.0" encoding="UTF-8"?>
<Workers>
    <Worker>
        <UniqueID>12354</UniqueID>
        <Date>2023-10-06-07:00</Date>
        <ExpenseAmount>658.00</ExpenseAmount>
        <BonusAmount>200.00</BonusAmount>
    </Worker>
    <Worker>
        <UniqueID>12354</UniqueID>
        <Date>2023-10-10-07:00</Date>
        <ExpenseAmount>220.00</ExpenseAmount>
        <BonusAmount>200.00</BonusAmount>
    </Worker>
    <Worker>
        <UniqueID>12354</UniqueID>
        <Date>2023-10-11-07:00</Date>
        <ExpenseAmount/>
        <BonusAmount>120.00</BonusAmount>
    </Worker>
    <Worker>
        <UniqueID>4567</UniqueID>
        <Date>2023-10-06-07:00</Date>
        <ExpenseAmount>210.00</ExpenseAmount>
        <BonusAmount>10.00</BonusAmount>
    </Worker>
    <Worker>
        <UniqueID>4567</UniqueID>
        <Date>2023-10-10-07:00</Date>
        <ExpenseAmount>1297.00</ExpenseAmount>
        <BonusAmount>312.00</BonusAmount>
    </Worker>
</Workers>

Here is my XSLT

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="xs" version="2.0">
    <xsl:output method="xml" indent="yes"/>
    <xsl:template match="Workers">
        <Workers>
            <xsl:for-each-group select="Worker" group-by="empID">
                <xsl:for-each-group select="current-group()" group-by="Cost/Date">
                    <Worker>
                        <UniqueID>
                            <xsl:value-of select="empID"/>
                        </UniqueID>
                        <Date>
                            <xsl:value-of select="current-grouping-key()"/>
                        </Date>
                        <ExpenseAmount>
                            <xsl:value-of
                                select="format-number(sum(current-group()/Cost[Type = 'Expense']/Amount), '#.00')"/>
                        </ExpenseAmount>
                        <BonusAmount>
                            <xsl:value-of select="format-number(sum(current-group()/Cost[Type = 'Bonus']/Amount), '#.00')"/>
                        </BonusAmount>
                    </Worker>
                </xsl:for-each-group>
            </xsl:for-each-group>
        </Workers>
    </xsl:template>
</xsl:stylesheet>

Current output

<?xml version="1.0" encoding="UTF-8"?>
<Workers>
   <Worker>
      <UniqueID>12354</UniqueID>
      <Date>2023-10-06-07:00</Date>
      <ExpenseAmount>998.00</ExpenseAmount>
      <BonusAmount>400.00</BonusAmount>
   </Worker>
   <Worker>
      <UniqueID>12354</UniqueID>
      <Date>2023-10-10-07:00</Date>
      <ExpenseAmount>998.00</ExpenseAmount>
      <BonusAmount>400.00</BonusAmount>
   </Worker>
   <Worker>
      <UniqueID>12354</UniqueID>
      <Date>2023-10-11-07:00</Date>
      <ExpenseAmount>998.00</ExpenseAmount>
      <BonusAmount>400.00</BonusAmount>
   </Worker>
   <Worker>
      <UniqueID>4567</UniqueID>
      <Date>2023-10-06-07:00</Date>
      <ExpenseAmount>1507.00</ExpenseAmount>
      <BonusAmount>322.00</BonusAmount>
   </Worker>
   <Worker>
      <UniqueID>4567</UniqueID>
      <Date>2023-10-10-07:00</Date>
      <ExpenseAmount>1507.00</ExpenseAmount>
      <BonusAmount>322.00</BonusAmount>
   </Worker>
</Workers>

Issue

ExpenseAmount and BonusAmount are incorrect

Could anyone help me figure out what I should be doing to get the sum value as provided in the expected output?

Answer 1

I suspect you only need to do:

XSLT 2.0

<xsl:stylesheet version="2.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>

<xsl:template match="Workers">
    <Workers>
        <xsl:for-each select="Worker">
            <xsl:variable name="empID" select="empID" />
            <xsl:for-each-group select="Cost" group-by="Date">
                <Worker>
                    <UniqueID>
                        <xsl:value-of select="$empID"/>
                    </UniqueID>
                    <Date>
                        <xsl:value-of select="current-grouping-key()"/>
                    </Date>
                    <ExpenseAmount>
                        <xsl:value-of select="format-number(sum(current-group()[Type = 'Expense']/Amount), '0.00')"/>
                    </ExpenseAmount>
                    <BonusAmount>
                        <xsl:value-of select="format-number(sum(current-group()[Type = 'Bonus']/Amount), '0.00')"/>
                    </BonusAmount>
                </Worker>
            </xsl:for-each-group>
        </xsl:for-each>
    </Workers>
</xsl:template>

</xsl:stylesheet>

---

To do it the way you started it would need to be:

<xsl:template match="Workers">
    <Workers>
        <xsl:for-each-group select="Worker" group-by="empID">
            <xsl:for-each-group select="current-group()/Cost" group-by="Date">
                <Worker>
                    <UniqueID>
                        <xsl:value-of select="../empID"/>
                    </UniqueID>
                    <Date>
                        <xsl:value-of select="current-grouping-key()"/>
                    </Date>
                    <ExpenseAmount>
                        <xsl:value-of select="format-number(sum(current-group()[Type = 'Expense']/Amount), '#.00')"/>
                    </ExpenseAmount>
                    <BonusAmount>
                        <xsl:value-of select="format-number(sum(current-group()[Type = 'Bonus']/Amount), '#.00')"/>
                    </BonusAmount>
                </Worker>
            </xsl:for-each-group>
        </xsl:for-each-group>
    </Workers>
</xsl:template>