This is the mail archive of the
xsl-list@mulberrytech.com
mailing list .
An XSLT equivalent of the SQL SUM and GROUP BY operations ?
- To: "'XSL-List at lists dot mulberrytech dot com'" <XSL-List at lists dot mulberrytech dot com>
- Subject: [xsl] An XSLT equivalent of the SQL SUM and GROUP BY operations ?
- From: Andy Tarpey <Andy_Tarpey at SystemsUnion dot com>
- Date: Mon, 21 May 2001 13:58:43 +0100
- Reply-To: xsl-list at lists dot mulberrytech dot com
Can anyone suggest how I might go about performing a transformation which is
the logical equivalent of a SQL statement like :
SELECT Account, Date, SUM(Amount)
FROM ...
GROUP BY Account, Date
An example of the type of transformation I am trying to achieve (which
matches the above SQL) is given below.
XML input :
<line>
<Account>64001</Account><Date>01/02/91</Date><Amount>50</Amount>
</line>
<line>
<Account>64001</Account><Date>01/02/91</Date><Amount>70</Amount>
</line>
<line>
<Account>64002</Account><Date>12/03/91</Date><Amount>60</Amount>
</line>
<line>
<Account>64002</Account><Date>01/02/91</Date><Amount>30</Amount>
</line>
<line>
<Account>64002</Account><Date>12/03/91</Date><Amount>90</Amount>
</line>
XML output :
<line>
<Account>64001</Account><Date>01/02/91</Date><Amount>120</Amount>
</line>
<line>
<Account>64002</Account><Date>01/02/91</Date><Amount>30</Amount>
</line>
<line>
<Account>64002</Account><Date>12/03/91</Date><Amount>150</Amount>
</line>
The transformation should sum all the Amounts for each matching Date within
each matching Account (i.e. Account 64001 shrinks to a single line as both
the dates are the same, but Account 64002 shrinks to two lines - one with
the sum of the amounts for 01/02/91 and one with the sum of the amounts for
12/03/91).
Any hints/suggestions/solutions for the specific example quoted or a more
generic solution (where parameters specify the nodes to group by and their
precedence) would be gratefully appreciated.
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list