counting sum with XSLT from XML row that have same items

Question

I'm sorry for my english, it's not native language so some word and term may be wrong.

I have XML where is many items. and some items might be many times. I want it to be hatml table, but so that same items are listet only once and the total has been summed up.

Normal for-each is easy, but it just lists everything. How can i add some condition etc for my XSLT?

Here is my example XML:

<Amounts>
    <item>
        <id>02</id>
        <name>Item2</name>
        <amount>20</amount>
    </item>
    <item>
        <id>01</id>
        <name>Item1</name>
        <amount>80</amount>
    </item>
    <item>
        <id>06</id>
        <name>Item6</name>
        <amount>50</amount>
    </item>
    <item>
        <id>02</id>
        <name>Item2</name>
        <amount>150</amount>
    </item>
</Amounts>

And here is my XSLT now:

<table>
<tr>
    <td>id</td>
    <td>name</td>
    <td>total</td>
</tr>
<xsl:for-each select="/Amounts/item">
<tr>
    <td><xsl:value-of select="id"/></td>
    <td><xsl:value-of select="name"/></td>
    <td><xsl:value-of select="amount"/></td>
</tr>
</table>

But here is how i wan't it to be:

<table>
<tr>
    <td>id</td>
    <td>name</td>
    <td>total</td>
</tr>
<tr>
    <td>01</td>
    <td>Item1</td>
    <td>80</td>
</tr>
<tr>
    <td>02</td>
    <td>Item2</td>
    <td>170</td>
</tr>
<tr>
    <td>06</td>
    <td>Item6</td>
    <td>50</td>
</tr>   
</table>

It is a grouping problem, it looks like you want to group the `item` elements by the `id` or by the `name` child element and then sum the `amout` of each group. See https://www.w3.org/TR/xslt20/#grouping-examples for XSLT 2.0 or http://www.jenitennison.com/xslt/grouping/muenchian.xml for XSLT 1.0 based Muenchian grouping. — Martin Honnen, May 10 '16 at 09:41

score 1 · Answer 1 · answered May 10 '16 at 11:49

Using Muenchian grouping you can solve that in XSLT 1.0 with

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">

    <xsl:output method="html"/>

    <xsl:key name="group" match="item" use="id"/>

    <xsl:template match="Amounts">
        <table>
            <thead>
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                    <th>Amount</th>
                </tr>
            </thead>
            <tbody>
                <xsl:apply-templates select="item[generate-id() = generate-id(key('group', id)[1])]">
                    <xsl:sort select="id" data-type="number"/>
                </xsl:apply-templates>
            </tbody>
        </table>
    </xsl:template>

    <xsl:template match="item">
        <tr>
            <td>
                <xsl:value-of select="id"/>
            </td>
            <td>
                <xsl:value-of select="name"/>
            </td>
            <td>
                <xsl:value-of select="sum(key('group', id)/amount)"/>
            </td>
        </tr>
    </xsl:template>

</xsl:stylesheet>

score 0 · Answer 2 · edited May 23 '17 at 11:52

XSLT 1.0: The principle idea of the solution is to use a key on the id attribute values, as described in https://stackoverflow.com/a/2293626/2156349 .

<xsl:stylesheet 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    version="1.0"
    >
    <xsl:output method="html" indent="yes" />       
    <xsl:strip-space elements="*"/>

    <xsl:key name="itemsKey" match="item/id/text()" use="." />

    <xsl:template match="/">
        <html>
        <head>  <title>Solution</title> </head>
        <body>  <xsl:apply-templates /> </body>
        </html>
    </xsl:template>

    <xsl:template match="Amounts">
        <table>
            <tr>
                <td>id</td>
                <td>name</td>
                <td>total</td>
            </tr>
            <xsl:for-each select="item">
                <xsl:variable name="idText" select="id/text()" />
                <xsl:variable name="myId"   select="generate-id($idText)" />
                <xsl:variable name="firstId" select="generate-id(key('itemsKey',$idText)[1])" />

                <xsl:if test="$myId = $firstId">
                    <xsl:apply-templates select="." />
                </xsl:if>
            </xsl:for-each>
        </table>
    </xsl:template>

    <xsl:template match="item">
        <xsl:variable name="idValue" select="id" />
        <tr>
            <td><xsl:value-of select="id"/></td>
            <td><xsl:value-of select="name"/></td>
            <td><xsl:value-of select="sum(//item[id=$idValue]/amount)"/></td>
        </tr>
    </xsl:template>

</xsl:stylesheet>

XSLT 2.0: Using the hint of Martin Honnen from above, the whole Transformation can be simplified considerably with XSLT 2.0:

<xsl:stylesheet 
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
    version="2.0"
    >
    <xsl:output method="html" indent="yes" />
    <xsl:strip-space elements="*"/>

    <xsl:template match="/">
        <html>
        <head> <title>Solution</title> </head>
        <body> <xsl:apply-templates /> </body>
        </html>
    </xsl:template>

    <xsl:template match="Amounts">
        <table>
            <tr>
                <td>id</td>
                <td>name</td>
                <td>total</td>
            </tr>
            <xsl:for-each-group select="item" group-by="id">
                <tr>
                    <td><xsl:value-of select="id"/></td>
                    <td><xsl:value-of select="name"/></td>
                    <td><xsl:value-of select="sum(current-group()/amount)"/></td>
                </tr>
            </xsl:for-each-group>
        </table>
    </xsl:template>

</xsl:stylesheet>

The whole `...` can be shortened to ``. And with the key being defined it should be used when computing the sum: ``. — Martin Honnen, May 10 '16 at 10:47
You are right. I first considered to use `apply-templates`. But I think the `for-each` variant is more readable. Using `sum(key('itemsKey', id)/amount)` didn't work, however. I do not understand why, however. — SomeStupid, May 10 '16 at 11:16
Sorry, Martin! Seems, we both added a "Muenchian solution" at the same time. I like the (your) Muenchian solution (XSLT 2) proposed initially in your first comment. — SomeStupid, May 10 '16 at 12:04

counting sum with XSLT from XML row that have same items

2 Answers2