This is the mail archive of the
xsl-list@mulberrytech.com
mailing list .
Re:Re:An XSLT equivalent of SQL SUM and GROUP BY operations?
- To: <xsl-list at lists dot mulberrytech dot com>
- Subject: [xsl] Re:Re:An XSLT equivalent of SQL SUM and GROUP BY operations?
- From: "Yang" <sfyang at unisvr dot net dot tw>
- Date: Wed, 23 May 2001 09:37:32 +0800
- Reply-To: xsl-list at lists dot mulberrytech dot com
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