Finding the most common value in an XML set

Teeeeej · December 16th, 2004, 05:10 PM

Hi,

I've been learning XSLT slowly and frustratingly all day and have managed to do 2 of the 3 queries for my assignment. The last one has left me totally stumped however.

My XML set is:

<?xml version="1.0" encoding="utf-8"?>
<properties xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="gspc.xsd">

    <house ref="80890">
        <street>Holmlea Road</street>
        <area>Cathcart</area>
        <house_no>59</house_no>
        <description>Great!</description>
        <price>68000</price>
        <bedrooms>3</bedrooms>
        <ea_ref id="Glass-McDowall"/>
    </house>
    <house ref="80891">
        <street>Castle Road</street>
        <area>Bridgeton</area>
        <house_no>21</house_no>
        <description>****e</description>
        <price>55000</price>
        <bedrooms>3</bedrooms>
        <ea_ref id="Estate-Agent-Co."/>
    </house>
    <house ref="80892">
        <street>Hilllview st</street>
        <area>Shettleston</area>
        <house_no>36</house_no>
        <description>Clean</description>
        <price>48000</price>
        <bedrooms>2</bedrooms>
        <ea_ref id="Glass-McDowall"/>
    </house>
    <house ref="80893">
        <street>Tynecastle cr</street>
        <area>Springboig</area>
        <house_no>78</house_no>
        <description>Large</description>
        <price>76000</price>
        <bedrooms>4</bedrooms>
        <ea_ref id="Glass-McDowall"/>
    </house>
    <house ref="80894">
        <street>Tolcross Rd</street>
        <area>Tollcross</area>
        <house_no>1048</house_no>
        <description>Needs Work</description>
        <price>30000</price>
        <bedrooms>4</bedrooms>
        <ea_ref id="Estate-Agent-Co."/>
    </house>
    <estate_agent id="Glass-McDowall">
        <name>Glass McDowall</name>
        <address>3 Blah Rd</address>
    </estate_agent>
    <estate_agent id="Estate-Agent-Co.">
        <name>Estate Agent Co.</name>
        <address>10 Commercial Lane</address>
    </estate_agent>
    <house ref="80892">
        <street>Pettigrew st</street>
        <area>Shettleston</area>
        <house_no>60</house_no>
        <description>Spacious</description>
        <price>49500</price>
        <bedrooms>2</bedrooms>
        <ea_ref id="Glass-McDowall"/>
    </house>
</properties>

And I have to find the most common agent used in an area. So basically get the ea_ref's id attribute, count how many times each unique id appears and then output the one with the highest. I could do this easily in Java/PHP/etc. but XSLT's static variables have finally defeated me and I don't know how to do it.

Can anyone help?

Thanks,
David

jkmyoung · December 16th, 2004, 06:23 PM

are you aware of the xpath count function?

eg. <xsl:value-of select="count(house[@ea_ref='Glass-McDowall'])"/>

you could also use muenchian grouping, but I think that's way too much effort.

http://www.jenitennison.com/xslt/gro...muenchian.html

mhkay · December 16th, 2004, 07:05 PM

A hint: count($x[@a=current()/@a]) gives you the number of elements in $x with the same value of @a as the current item. Sort on this, and take the first or last item in the sorted sequence.

Michael Kay
http://www.saxonica.com/

Teeeeej · December 16th, 2004, 07:12 PM

Thanks Mike... I did a similar thing to get the lowest cost house in the set, didn't think of it at all for this one.

Thanks for the help both of you!

Teeeeej · December 16th, 2004, 07:33 PM

Hmmm... can't get it to work. Perhaps I don't understand.. could you explain it a bit further?

jkmyoung · December 16th, 2004, 07:43 PM

can you post what you have, so we can see what you're missing?

Teeeeej · December 16th, 2004, 07:58 PM

Code:

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

    1.The average price of a house in a specific area (Shettleston).
    <xsl:variable name="totalprice">
        <xsl:value-of select = "sum(//price[../area='Shettleston'])" />
    </xsl:variable>
    <xsl:variable name="noofhouse">
        <xsl:value-of select = "count(//house[area='Shettleston'])" />
    </xsl:variable>    
    Total Price:  <xsl:value-of select = "$totalprice" />      
    Number of Houses: <xsl:value-of select = "$noofhouse" />

    Average Price: <xsl:value-of select = "$totalprice div $noofhouse" />

    #######################

    2.The most common agent used in a Glasgow(or any area).

        <xsl:variable name="minimum">
        <xsl:for-each select="//house/@ref">
            <xsl:sort : select="count(//house[@ea_ref=current()/@ea_ref])" order="ascending" />
            <xsl:if test="position() = 1">
                <xsl:value-of select="." />
            </xsl:if>
        </xsl:for-each>
    </xsl:variable>

    <xsl:value-of select = "$minimum" />

    #######################

    3.The house ref of the cheapest house on market
    <xsl:variable name="minimum">
        <xsl:for-each select="//house/@ref">
            <xsl:sort select="../price" order="ascending" />
            <xsl:if test="position() = 1">
                <xsl:value-of select="." />
            </xsl:if>
        </xsl:for-each>
    </xsl:variable>

    <xsl:value-of select = "$minimum" />


   </xsl:template>
</xsl:stylesheet>

That's my XSL file, QUERY #2 is the one I'm having problems with.. I realise what I have right now doesn't make sense.. I've been constantly trying to edit stuff and I tried editing the third query with mike's hint but I'm stuck and my head hurts :(

pgtips · December 17th, 2004, 08:59 AM

Quote:

quote:
2.The most common agent used in a Glasgow(or any area).

        <xsl:variable name="minimum">
        <xsl:for-each select="//house/@ref">
            <xsl:sort : select="count(//house[@ea_ref=current()/@ea_ref])" order="ascending" />
            <xsl:if test="position() = 1">
                <xsl:value-of select="." />
            </xsl:if>
        </xsl:for-each>
    </xsl:variable>

    <xsl:value-of select = "$minimum" />

a few more hints:
1. look at your xsl:for-each, which defines the context node, do you really want it to be the ref attribute?
2. look at your xml - do you really have an attribute named ea_ref in the current context (or indeed in any context)?

hth
Phil