Subject: Re: [xsl] An XSLT equivalent of the SQL SUM and GROUP BY operations ? From: Jeni Tennison <mail@xxxxxxxxxxxxxxxx> Date: Mon, 21 May 2001 15:08:30 +0100 |
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
Current Thread |
---|
|
<- Previous | Index | Next -> |
---|---|---|
[xsl] An XSLT equivalent of the SQL, Andy Tarpey | Thread | RE: [xsl] An XSLT equivalent of the, Michael Kay |
[xsl] An XSLT equivalent of the SQL, Andy Tarpey | Date | [xsl] xsl:if test... Doesn't like t, gpk17 |
Month |