Re: An XSLT equivalent of the SQL SUM and GROUP BY ope

Hi Andy,

This is a grouping problem, and as such I'd suggest that you use the
Muenchian Method for identifying the groups.

First, define a key that enables you to rapidly get all the lines with
a particular Account/Date combination.  You can specify the
Account/Date combination by concatenating the values of the two
elements together (with some appropriate separator).  For example:

<xsl:key name="lines"
         match="line"
         use="concat(Account, ':', Date)" />

With this key in place, you can get, for example, all the lines for
Account 64001 on 01/02/91 with:

  key('lines', '64001:01/02/91')

Once you have all these line elements, you can sum their Amount
children using the sum() function:

  sum(key('lines', '64001:01/02/91')/Amount)

So, that's well and good if you know the Account and Dates that you
want to access, but you don't know that in advance, so you need to
have some way of going through all the line elements to find those
that have unique Account/Date combinations and process them.

The unique line elements may as well be the first line elements with a
particular Account/Date combination; given a line element ($line), you
can check whether it's the same as the first one returned by the key
with that line's Account/Date combo by comparing their unique IDs
with:

  generate-id($line) =
  generate-id(key('lines', concat($line/Account, ':' $line/Date))[1])

or using set logic (if you union two sets that each contain the same
single node within them, then the union will only have one node in it;
if they're different it'll have two):

  count($line |
        key('lines', concat($line/Account, ':', $line/Date))[1]) = 1

So, what you need to do is go through all the line elements in turn
and check out whether they're the first returned by the key.  These
are the only ones you want to process, so apply templates to only
them:

  <xsl:apply-templates
     select="line[count(.|key('lines', concat(Account, ':', Date))[1])
                  = 1]" />

Then you want a template for the line elements that basically copies
it, but has the Amount element give the sum of the Amounts from all
the lines with that Amount/Date pair:

<xsl:template match="line">
   <line>
      <xsl:copy-of select="Account | Date" />
      <Amount>
         <xsl:value-of
            select="sum(key('lines',
                            concat(Account, ':', Date))/Amount)" />
      </Amount>
   </line>
</xsl:template>

I hope that helps,

Jeni

---
Jeni Tennison
http://www.jenitennison.com/



 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list