Re: [xsl] Sum one attribute while grouping by another

Subject: Re: [xsl] Sum one attribute while grouping by another
From: Wendell Piez <wapiez@xxxxxxxxxxxxxxxx>
Date: Thu, 24 Jul 2008 15:44:18 -0400

If you can't use XSLT 2.0, you would be best off in 1.0 using the so-called Muenchian grouping technique to collect your groups. It would allow the collection of nodes that you need. It is also more efficient and will perform better over a large data set than the method you are using. (Actually, at present you aren't grouping at all, as is indicated by your dependency on the items being sorted by type -- you are only using comparison logic to recognize the start and end of groups that are already implicit in the order. A grouping algorithm would be able to work with groups that were not sorted in the input.)

The essence of Muenchian grouping is in the use of key-based retrieval to collect all the elements belonging to a particular group. For example, if you had

<xsl:key name="items-by-type" match="item" use="@type"/>

you would then be able to collect all the 'fruit' items using "key('items-by-type','fruit')" and all the items of type $x using "key('items-by-type',$x)".

Not only is this useful for grouping, but it's helpful for aggregating, as you can then say "sum(key('items-by-type','fruit')/@rotten)" to add together the @rotten attributes on the 'fruit' items.

Muenchian grouping is well documented on line. Besides key-based retrieval, the one subtlety it depends on is an idiom to recognize a single representative member of each group (commonly the first one). Usually, for this you'll see something like


which will be true only for every first item of its type. You need such a de-duplication technique to avoid repeating groups.

If this plus your further research leaves you confused, feel free to ask for more detail or clarification.


At 02:06 PM 7/24/2008, you wrote:
How do I sum one attribute while grouping by another?

From examples online (including posts in this list) I'm pretty sure a recursive template is necessary but I can't figure out how. Unfortunately, all the examples are too simple (e.g. increment counter with position()) or assume you can divide the data by elements. All my elements are the same.

For instance, I have the following XML data:

 <item type="fruit" name="apple" rotten="6" total="10"/>
 <item type="fruit" name="orange" rotten="2" total="10"/>
 <item type="fruit" name="pear" rotten="1" total="4"/>
 <item type="vegetable" name="carrot" rotten="0" total="5"/>
 <item type="vegetable" name="potato" rotten="1" total="2"/>

To which I apply a stylesheet to get a nice table output. I know how to get to overall sums, but I don't know how to get the group/inner sums (denoted by '?'):

type    name    rotten  total   percent
        apple   6       10      60
        orange  2       10      20
        pear    1       4       25
        total   ?       ?       ?
        carrot  0       5       0
        potato  1       2       50
        total   ?       ?       ?
total           10      31      32.3

Here are some assumptions that can be made about the data:
* The items are guaranteed to be sorted by the "type" attribute.
* I don't know the possible values of "type" beforehand.
* Its format cannot be changed.

* I am using XSLT 1.0, but upgrading to 2.0 might be possible (I'm not sure).
* I am using python 2.4 with libxml2 and libxslt.

Here's the stylesheet I have now (that generated the above table):

<xsl:stylesheet version="1.0" xmlns:xsl="";>
<xsl:template match="/">

   <xsl:for-each select="//item">
     <xsl:if test="not(preceding-sibling::*[1]/@type=@type)">
       <tr><td><xsl:value-of select="@type"/></td></tr>

<td><xsl:value-of select="@name"/></td>
<td><xsl:value-of select="@rotten"/></td>
<td><xsl:value-of select="@total"/></td>
<td><xsl:value-of select="format-number(@rotten div @total * 100,'##.#')"/></td>

     <xsl:if test="not(following-sibling::*[1]/@type=@type)">

<td><xsl:value-of select="sum(//item/@rotten)"/></td>
<td><xsl:value-of select="sum(//item/@total)"/></td>
<td><xsl:value-of select="format-number(sum(//item/@rotten) div sum(//item/@total) * 100,'##.#')"/></td>

Wendell Piez                            mailto:wapiez@xxxxxxxxxxxxxxxx
Mulberry Technologies, Inc.      
17 West Jefferson Street                    Direct Phone: 301/315-9635
Suite 207                                          Phone: 301/315-9631
Rockville, MD  20850                                 Fax: 301/315-8285
  Mulberry Technologies: A Consultancy Specializing in SGML and XML

Current Thread