Grouping and Sum problem

Discussion in 'XML' started by Bryce (Work), Sep 30, 2003.

  1. Bryce (Work)

    Bryce (Work) Guest

    Here is the xml I'm working with:

    <result>
    <EFORM>
    <testNode>
    <section1>
    <string1>ADAM</string1>
    <integer1>55</integer1>
    </section1>
    </testNode>
    </EFORM>
    <EFORM>
    <testNode>
    <section1>
    <string1>JOHN</string1>
    <integer1>43</integer1>
    </section1>
    </testNode>
    </EFORM>
    <EFORM>
    <testNode>
    <section1>
    <string1>ADAM</string1>
    <integer1>22</integer1>
    </section1>
    </testNode>
    </EFORM>
    <EFORM>
    <testNode>
    <section1>
    <string1>JOHN</string1>
    <integer1>15</integer1>
    </section1>
    </testNode>
    </EFORM>
    </result>

    What I'm trying to do is group by <string1> and sum <integer1>

    for example:
    JOHN 58
    ADAM 77

    Using Muenchian method, I can count the number of nodes for each
    group, but am having difficulty getting the sum. Since I don't 100%
    understand the code to do the grouping, I'm having difficutly. Here's
    what I have for Counting:

    <xslout:key name="keyname" match="EFORM"
    use="testNode/Section1/string1" />

    <xslout:template match="/">
    <summary>
    <xslout:for-each select="//EFORM[count(. | key('keyname',
    testNode/Section1/string1)[1]) = 1]">
    <xslout:sort select="testNode/Section1/string1"/>
    <summaryGroup>
    <summaryField>
    <xslout:value-of select="testNode/Section1/string1"/>
    </summaryField>
    <summaryvalue>
    <xslout:value-of select="count(key('keyname',
    testNode/Section1/string1))"/>
    </summaryvalue>
    </summaryGroup>
    </xslout:for-each>
    </summary>
    </xslout:template>

    I'm not sure what I shoudl put in to do the sum... Or even if I can do
    it this way.

    Any help is appreciated.

    Bryce
     
    Bryce (Work), Sep 30, 2003
    #1
    1. Advertising

  2. Use:

    <xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:eek:utput method="text"/>

    <xsl:key name="kStr" match="string1" use="."/>

    <xsl:template match="/">
    <xsl:for-each
    select="/*/*/*/*/string1
    [generate-id()
    =
    generate-id(key('kStr', .)[1])
    ]">
    <xsl:value-of
    select="concat('
    ', ., ' ',
    sum(key('kStr', .)/../integer1)
    )"/>
    </xsl:for-each>
    </xsl:template>
    </xsl:stylesheet>

    When this transformation is applied on your source.xml, the wanted result is
    produced:

    ADAM 77
    JOHN 58


    =====
    Cheers,

    Dimitre Novatchev.
    http://fxsl.sourceforge.net/ -- the home of FXSL


    "Bryce (Work)" <> wrote in message
    news:...
    > Here is the xml I'm working with:
    >
    > <result>
    > <EFORM>
    > <testNode>
    > <section1>
    > <string1>ADAM</string1>
    > <integer1>55</integer1>
    > </section1>
    > </testNode>
    > </EFORM>
    > <EFORM>
    > <testNode>
    > <section1>
    > <string1>JOHN</string1>
    > <integer1>43</integer1>
    > </section1>
    > </testNode>
    > </EFORM>
    > <EFORM>
    > <testNode>
    > <section1>
    > <string1>ADAM</string1>
    > <integer1>22</integer1>
    > </section1>
    > </testNode>
    > </EFORM>
    > <EFORM>
    > <testNode>
    > <section1>
    > <string1>JOHN</string1>
    > <integer1>15</integer1>
    > </section1>
    > </testNode>
    > </EFORM>
    > </result>
    >
    > What I'm trying to do is group by <string1> and sum <integer1>
    >
    > for example:
    > JOHN 58
    > ADAM 77
    >
    > Using Muenchian method, I can count the number of nodes for each
    > group, but am having difficulty getting the sum. Since I don't 100%
    > understand the code to do the grouping, I'm having difficutly. Here's
    > what I have for Counting:
    >
    > <xslout:key name="keyname" match="EFORM"
    > use="testNode/Section1/string1" />
    >
    > <xslout:template match="/">
    > <summary>
    > <xslout:for-each select="//EFORM[count(. | key('keyname',
    > testNode/Section1/string1)[1]) = 1]">
    > <xslout:sort select="testNode/Section1/string1"/>
    > <summaryGroup>
    > <summaryField>
    > <xslout:value-of select="testNode/Section1/string1"/>
    > </summaryField>
    > <summaryvalue>
    > <xslout:value-of select="count(key('keyname',
    > testNode/Section1/string1))"/>
    > </summaryvalue>
    > </summaryGroup>
    > </xslout:for-each>
    > </summary>
    > </xslout:template>
    >
    > I'm not sure what I shoudl put in to do the sum... Or even if I can do
    > it this way.
    >
    > Any help is appreciated.
    >
    > Bryce
     
    Dimitre Novatchev, Sep 30, 2003
    #2
    1. Advertising

  3. Bryce (Work)

    Marrow Guest

    Hi Bryce,

    The case in some of your XPath expressions may be causing some problems (you
    have "Section1" in places where you probably want "section1" - as per the
    input XML).

    This XSL works...

    <?xml version="1.0"?>
    <xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:eek:utput method="xml" indent="yes"/>
    <xsl:key name="keyname" match="EFORM" use="testNode/section1/string1" />
    <xsl:template match="/">
    <summary>
    <xsl:for-each select="result/EFORM[generate-id() =
    generate-id(key('keyname',testNode/section1/string1))]">
    <xsl:sort select="testNode/section1/string1"/>
    <summaryGroup>
    <summaryField>
    <xsl:value-of select="testNode/section1/string1"/>
    </summaryField>
    <summaryvalue>
    <xsl:value-of
    select="sum(key('keyname',testNode/section1/string1)/testNode/section1/integ
    er1)"/>
    </summaryvalue>
    </summaryGroup>
    </xsl:for-each>
    </summary>
    </xsl:template>
    </xsl:stylesheet>

    Hope this helps
    Marrow
    http://www.marrowsoft.com - home of Xselerator (XSLT IDE and debugger)
    http://www.topxml.com/Xselerator


    "Bryce (Work)" <> wrote in message
    news:...
    > Here is the xml I'm working with:
    >
    > <result>
    > <EFORM>
    > <testNode>
    > <section1>
    > <string1>ADAM</string1>
    > <integer1>55</integer1>
    > </section1>
    > </testNode>
    > </EFORM>
    > <EFORM>
    > <testNode>
    > <section1>
    > <string1>JOHN</string1>
    > <integer1>43</integer1>
    > </section1>
    > </testNode>
    > </EFORM>
    > <EFORM>
    > <testNode>
    > <section1>
    > <string1>ADAM</string1>
    > <integer1>22</integer1>
    > </section1>
    > </testNode>
    > </EFORM>
    > <EFORM>
    > <testNode>
    > <section1>
    > <string1>JOHN</string1>
    > <integer1>15</integer1>
    > </section1>
    > </testNode>
    > </EFORM>
    > </result>
    >
    > What I'm trying to do is group by <string1> and sum <integer1>
    >
    > for example:
    > JOHN 58
    > ADAM 77
    >
    > Using Muenchian method, I can count the number of nodes for each
    > group, but am having difficulty getting the sum. Since I don't 100%
    > understand the code to do the grouping, I'm having difficutly. Here's
    > what I have for Counting:
    >
    > <xslout:key name="keyname" match="EFORM"
    > use="testNode/Section1/string1" />
    >
    > <xslout:template match="/">
    > <summary>
    > <xslout:for-each select="//EFORM[count(. | key('keyname',
    > testNode/Section1/string1)[1]) = 1]">
    > <xslout:sort select="testNode/Section1/string1"/>
    > <summaryGroup>
    > <summaryField>
    > <xslout:value-of select="testNode/Section1/string1"/>
    > </summaryField>
    > <summaryvalue>
    > <xslout:value-of select="count(key('keyname',
    > testNode/Section1/string1))"/>
    > </summaryvalue>
    > </summaryGroup>
    > </xslout:for-each>
    > </summary>
    > </xslout:template>
    >
    > I'm not sure what I shoudl put in to do the sum... Or even if I can do
    > it this way.
    >
    > Any help is appreciated.
    >
    > Bryce
     
    Marrow, Sep 30, 2003
    #3
  4. Bryce (Work)

    Bryce (Work) Guest

    On Tue, 30 Sep 2003 22:50:55 +0100, "Marrow"
    <> wrote:

    >Hi Bryce,
    >

    [snip]
    >Hope this helps
    >Marrow
    >http://www.marrowsoft.com - home of Xselerator (XSLT IDE and debugger)
    >http://www.topxml.com/Xselerator


    Yes indeed. And I even figured out why it works :) I just had to
    figure out where the root was, and XPath to th at...

    Thanks to everyone for their help
     
    Bryce (Work), Oct 1, 2003
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Mike King

    Grouping problem

    Mike King, Jan 6, 2005, in forum: XML
    Replies:
    5
    Views:
    455
    David Carlisle
    Jan 6, 2005
  2. Replies:
    0
    Views:
    1,324
  3. cieron
    Replies:
    2
    Views:
    727
    Dimitre Novatchev
    May 16, 2005
  4. Shreyas Kulkarni
    Replies:
    1
    Views:
    954
    Gregory Toomey
    Dec 27, 2004
  5. David

    Grouping & Sum of result page

    David, Sep 25, 2006, in forum: ASP General
    Replies:
    5
    Views:
    138
    David
    Sep 25, 2006
Loading...

Share This Page