Performing a “Group By” with sum function in XSLT 1.0

Question

I want a result organized like following table:

**Costc 1000**          
    Product code    Quantity    Total amount

    SALESCOST       1   120.04
    LEASINGRENT     1   59.90

**Costc 2000**          
    Product code    Quantity    Total amount

    SALESCOST       1   118.94
    LEASINGCOST     1   513.34

**Costc 3000**          
    Product code    Quantity    Total amount

    LEASINGCOST     1   658.24
    LEASINGRENT     2   100.80

And the input-file I have look like this.

<?xml version="1.0" encoding="iso-8859-1"?>
<Details>
    <Detail>
        <LineNo>1</LineNo>
    <Products>
        <ProductCode>SALESCOST</ProductCode>
        <ProductDescr>Sales amount asset:997000000000</ProductDescr>
        <Quantity>1.00</Quantity>
    </Products>
    <Costc>
        <Value>2000</Value>
    </Costc>
    <TotAmount>118.94</TotAmount>
</Detail>
<Detail>
    <LineNo>2</LineNo>
    <Products>
        <ProductCode>LEASINGCOST</ProductCode>
        <ProductDescr>Leasing cost asset:997000000003</ProductDescr>
    </Products>
    <Costc>
        <Value>2000</Value>
    </Costc>
    <Quantity>1.00</Quantity>
    <TotAmount>513.34</TotAmount>
</Detail>
<Detail>
    <LineNo>3</LineNo>
    <Products>
        <ProductCode>SALESCOST</ProductCode>
        <ProductDescr>Sales amount asset:997000000001</ProductDescr>
    </Products>
    <Costc>
        <Value>1000</Value>
    </Costc>
    <Quantity>1.00</Quantity>
    <TotAmount>120.04</TotAmount>
</Detail>
<Detail>
    <LineNo>4</LineNo>
    <Products>
        <ProductCode>LEASINGCOST</ProductCode>
        <ProductDescr>Leasing cost asset:997000000002</ProductDescr>
    </Products>
    <Costc>
        <Value>3000</Value>
    </Costc>
    <Quantity>1.00</Quantity>
    <TotAmount>658.24</TotAmount>
</Detail>
<Detail>
    <LineNo>5</LineNo>
    <Products>
        <ProductCode>LEASINGRENT</ProductCode>
        <ProductDescr>Leasing interest asset:997000000001</ProductDescr>
    </Products>
    <Costc>
        <Value>3000</Value>
    </Costc>
    <Quantity>1.00</Quantity>
    <TotAmount>48.90</TotAmount>
</Detail>
<Detail>
    <LineNo>6</LineNo>
    <Products>
        <ProductCode>LEASINGRENT</ProductCode>
        <ProductDescr>Leasing interest asset:997000000002</ProductDescr>
    </Products>
    <Costc>
        <Value>3000</Value>
    </Costc>
    <Quantity>1.00</Quantity>
    <TotAmount>51.90</TotAmount>
</Detail>
<Detail>
    <LineNo>7</LineNo>
    <Products>
        <ProductCode>LEASINGRENT</ProductCode>
        <ProductDescr>Leasing interest asset:997000000002</ProductDescr>
    </Products>
    <Costc>
        <Value>1000</Value>
    </Costc>
    <Quantity>1.00</Quantity>
    <TotAmount>59.90</TotAmount>
</Detail>

How can this be done? Its multi-level group by and also sums and count function in this and it’s too complex for me. Many thanks

If quantities can indeed be set at two levels, then Tim's XSLT 1.0 solution will have to be adjusted (see the result, where quanity for 2000/SALESCOST is 0), taking into account the second level (within products). Note that Lukasz sums quantities on both levels already. — Maestro13, Feb 10 '12 at 06:39
I've adjusted my answer to cope with Quantity on two different levels. — Tim C, Feb 10 '12 at 07:23

Tim C · Accepted Answer · 2012-02-10T07:22:40.393

In XSLT 1.0 you would need to use a technique called Meunchian Grouping. In this case you are grouping firstly by Costc elements, and then by Products elements.

This means you need to define two keys. Firstly to group details by Costc

<xsl:key name="costc" match="Detail" use="Costc/Value"/>

And then to group details by Costc and Product

<xsl:key name="product" match="Detail" use="concat(Costc/Value, '|', Products/ProductCode)"/>

(Do note the use of the pipe character in the concatenation. It is important this character does not appear in the value or the product code).

Then, to group by the Costc elements, you can do the following:

<xsl:apply-templates 
   select="Detail[generate-id() = generate-id(key('costc', Costc/Value)[1])]">

This would get you the first Detail element for each unique Costc element. Then, for each such group, you would then group by the products within that group.

<xsl:apply-templates 
   select="../Detail
      [Costc/Value = current()/Costc/Value]
      [generate-id() 
         = generate-id(key('product', concat(Costc/Value, '|', Products/ProductCode))[1])]"  />

So, given the following XSLT

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

   <xsl:key name="costc" match="Detail" use="Costc/Value"/>
   <xsl:key name="product" match="Detail" use="concat(Costc/Value, '|', Products/ProductCode)"/>

   <xsl:template match="/Details">
      <xsl:apply-templates select="Detail[generate-id() = generate-id(key('costc', Costc/Value)[1])]" mode="Cost">
         <xsl:sort select="Costc/Value" />
      </xsl:apply-templates>
   </xsl:template>

   <xsl:template match="Detail" mode="Cost">
      <h1><xsl:value-of select="Costc/Value" /></h1>
      <table>
         <tr>
            <td>Product Code</td>
            <td>Quantity</td>
            <td>Total amount</td>
         </tr>
         <xsl:apply-templates select="../Detail[Costc/Value = current()/Costc/Value][generate-id() = generate-id(key('product', concat(Costc/Value, '|', Products/ProductCode))[1])]" mode="Product" />
      </table>
   </xsl:template>

   <xsl:template match="Detail" mode="Product">
         <tr>
            <td><xsl:value-of select="Products/ProductCode" /></td>
            <td><xsl:value-of select="sum(key('product', concat(Costc/Value, '|', Products/ProductCode))//Quantity)" /></td>
            <td><xsl:value-of select="sum(key('product', concat(Costc/Value, '|', Products/ProductCode))/TotAmount)" /></td>
         </tr>
   </xsl:template>
</xsl:stylesheet>

When applied to your sample XML, the following is output:

<h1>1000</h1>
<table>
    <tr>
        <td>Product Code</td>
        <td>Quantity</td>
        <td>Total amount</td>
    </tr>
    <tr>
        <td>SALESCOST</td>
        <td>1</td>
        <td>120.04</td>
    </tr>
    <tr>
        <td>LEASINGRENT</td>
        <td>1</td>
        <td>59.9</td>
    </tr>
</table>
<h1>2000</h1>
<table>
    <tr>
        <td>Product Code</td>
        <td>Quantity</td>
        <td>Total amount</td>
    </tr>
    <tr>
        <td>SALESCOST</td>
        <td>1</td>
        <td>118.94</td>
    </tr>
    <tr>
        <td>LEASINGCOST</td>
        <td>1</td>
        <td>513.34</td>
    </tr>
</table>
<h1>3000</h1>
<table>
    <tr>
        <td>Product Code</td>
        <td>Quantity</td>
        <td>Total amount</td>
    </tr>
    <tr>
        <td>LEASINGCOST</td>
        <td>1</td>
        <td>658.24</td>
    </tr>
    <tr>
        <td>LEASINGRENT</td>
        <td>2</td>
        <td>100.8</td>
    </tr>
</table>

Many thanks for the quick answer. It’s work perfect. I didn’t actually thought I would get an answer at all. Now I get I quick and also a good answer. So I am very happy and have a tear in my eye of gratitude. Best regards from Sweden — user1200589, Feb 10 '12 at 07:52
@user1200589: "tear in my eye of gratitude" -- So exciting... At So users typically *accept* the best answer. I hope the tears in your eyes won't prevent you from accepting the answer? :) Hint: click on the check-mark next to the answer. — Dimitre Novatchev, Feb 10 '12 at 13:15
@DimitreNovatchev ahhh...how SO used to be :) Gratitude, nice answers, etc. Maybe we will get back there someday! — Jedidja, Nov 07 '19 at 23:26
@Jedidja: Why do we need to wait? We are all human beings and can act and express feelings in a natural way. SO is just the mechanism that connects us together. — Dimitre Novatchev, Nov 08 '19 at 00:05

score 1 · Answer 2 · answered Feb 09 '12 at 21:56

How about this?

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" >
    <xsl:output method="text"/>

    <xsl:template match="/">
        <xsl:for-each-group select="*/Detail" group-by="Costc/Value" >
            <xsl:sort select="current-grouping-key()" order="ascending"/>

            <xsl:value-of select="concat('** Costc ',current-grouping-key())"/>
            <xsl:text>&#xD;&#xA;  Product code    Quantity    Total amount&#xD;&#xA;</xsl:text>

            <xsl:for-each-group select="current-group()" group-by="Products/ProductCode" >
                <xsl:text>   </xsl:text>            
                <xsl:value-of select="current-grouping-key()"/>
                <xsl:text>   </xsl:text>
                <xsl:value-of select="number(sum(current-group()/Quantity, current-group()/Products/Quantity))" />              
                <xsl:text>   </xsl:text>
                                <xsl:value-of select="sum(current-group()/TotAmount)" />                
                <xsl:text>&#xD;&#xA;</xsl:text>             
            </xsl:for-each-group>       

            <xsl:text>&#xD;&#xA;</xsl:text>

        </xsl:for-each-group>
    </xsl:template>
</xsl:stylesheet>

You would have to work on adjusting formatting. This XSLT presents only algorithm. This is my output:

** Costc 1000
  Product code    Quantity    Total amount
   SALESCOST   1   120.04
   LEASINGRENT   1   59.9

** Costc 2000
  Product code    Quantity    Total amount
   SALESCOST   1   118.94
   LEASINGCOST   1   513.34

** Costc 3000
  Product code    Quantity    Total amount
   LEASINGCOST   1   658.24
   LEASINGRENT   2   100.8

Performing a “Group By” with sum function in XSLT 1.0

2 Answers2