This is the mail archive of the xsl-list@mulberrytech.com mailing list .


Index Nav: [Date Index] [Subject Index] [Author Index] [Thread Index]
Message Nav: [Date Prev] [Date Next] [Thread Prev] [Thread Next]

Re:Re:An XSLT equivalent of SQL SUM and GROUP BY operations?


Hi, all,

Jeni have given  basic elements for this data base problem.
Now on the top of her suggestions,  I add **SORT BY SUM** request to
the SQL  equivalent problem.  Hope Jeni can provide more input to my
addition.

To gain efficiency by saving computed effort with sum() function, I am using
two step approach.  The first step is to get data ready from using
apply-templates element

and then wrap the data with variable **tempResult**.

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

In the second step, it is to sort and display the solution using parser
related extension node-set function,  such as:

<xsl:apply-templates select="msxsl:node-set($tempResult)/line" mode="table">
        <xsl:sort select="Amount" order="descending" data-type="number"/>
</xsl:apply-templates>

Hope it will be helpful.

Sun-fu Yang

sfyang@unisvr.net.tw
<?xml version="1.0"?>

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
 xmlns:msxsl="urn:schemas-microsoft-com:xslt" >
<xsl:output method="html" indent="yes"/>


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

<xsl:template match="/">
   <html>
      <head>
      </head>
  <xsl:variable name="tempResult">
  <xsl:apply-templates
     select="//line[count(.|key('lines', concat(Account, ':', Date))[1])
                  = 1]" mode="unique"/>

  <!--xsl:apply-templates
     select="//line[generate-id(.) =
  generate-id(key('lines', concat($line/Account, ':' $line/Date))[1])
                  = 1]" /-->
 </xsl:variable>
  <table>
  <tr><th>account</th><th>date</th><th>amount</th></tr>
  <xsl:apply-templates select="msxsl:node-set($tempResult)/line"
mode="table">
 <xsl:sort select="Amount" order="descending" data-type="number"/>
  </xsl:apply-templates>
  </table>

</html>
</xsl:template>

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

<xsl:template match="line" mode="table">

<tr>
<td><xsl:value-of select="Account"/></td>
<td><xsl:value-of select="Date"/></td>
<td><xsl:value-of select="Amount"/></td>
</tr>
</xsl:template>

</xsl:stylesheet>


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


Index Nav: [Date Index] [Subject Index] [Author Index] [Thread Index]
Message Nav: [Date Prev] [Date Next] [Thread Prev] [Thread Next]