Re: Sum one attribute while grouping by another

Play the video

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

DB,

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

generate-id(.)=generate-id(key('items-by-type',@type)[1]

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.

Cheers,
Wendell

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:
<food>
 <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"/>
</food>
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
fruit
        apple   6       10      60
        orange  2       10      20
        pear    1       4       25
        total   ?       ?       ?
vegetable
        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.
Also:
* 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="http://www.w3.org/1999/XSL/Transform"> <xsl:template match="/"> <html><body><table> <tr> <td>type</td> <td>name</td> <td>rotten</td> <td>total</td> <td>percent</td> </tr>
   <xsl:for-each select="//item">
     <xsl:if test="not(preceding-sibling::*[1]/@type=@type)">
       <tr><td><xsl:value-of select="@type"/></td></tr>
     </xsl:if>
<tr> <td></td> <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> </tr>
     <xsl:if test="not(following-sibling::*[1]/@type=@type)">
       <tr>
         <td></td>
         <td>total</td>
         <td>?</td>
         <td>?</td>
         <td>?</td>
       </tr>
     </xsl:if>
   </xsl:for-each>
<tr> <td>total</td> <td></td> <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> </tr> </table></body></html> </xsl:template> </xsl:stylesheet>

======================================================================
Wendell Piez                            mailto:wapiez@xxxxxxxxxxxxxxxx
Mulberry Technologies, Inc.                http://www.mulberrytech.com
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
Sum one attribute while grouping by another DB - 24 Jul 2008 18:07:24 -0000 Wendell Piez - 24 Jul 2008 19:44:45 -0000 <=

<- Previous	Index	Next ->
Sum one attribute while group, DB	Thread	impossible to implement this , Tom Schmitter
Sum one attribute while group, DB	Date	impossible to implement this , Tom Schmitter
	Month

PURCHASE STYLUS STUDIO ONLINE TODAY!

Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!

Download The World's Best XML IDE!

Accelerate XML development with our award-winning XML IDE - Download a free trial today!

Subscribe in XML format

RSS 2.0
Atom 0.3

XML Editor - Download a 15 Day Free Trial Now >

See What's New in Stylus Studio >

Buy Stylus Studio - XML Editor - Now >