XPath find maximum sum by department (several departments)

Question

How find most expensive department?

I need to find the most expensive department (deptno) with use XPath 1.0. Structure of the my XML document next.

    <employee deptno="10" empno="7369" mgr="7902">
        <ename>SMITH</ename>
        <sal>800.00</sal>
    </employee>
    <employee deptno="30" empno="7499" mgr="7698">
        <ename>ALLEN</ename>
        <sal>1600.00</sal>
    </employee>
    <employee deptno="30" empno="7521" mgr="7698">
        <ename>WARD</ename>
        <sal>1250.00</sal>
    </employee>
    <employee deptno="20" empno="7566" mgr="7839">
        <ename>JONES</ename>
        <sal>2975.00</sal>
    </employee>
    <employee deptno="30" empno="7654" mgr="7698">
        <ename>MARTIN</ename>
        <sal>1250.00</sal>
    </employee>
    <employee deptno="30" empno="7698" mgr="7839">
        <ename>BLAKE</ename>
        <sal>2850.00</sal>
    </employee>
    <employee deptno="10" empno="7782" mgr="7839">
        <ename>CLARK</ename>
        <sal>2450.00</sal>
    </employee>
    <employee deptno="20" empno="7788" mgr="7566">
        <ename>SCOTT</ename>
        <sal>3000.00</sal>
    </employee>
    <employee deptno="10" empno="7839">
        <ename>KING</ename>
        <sal>5000.00</sal>
    </employee>
    <employee deptno="30" empno="7844" mgr="7698">
        <ename>TURNER</ename>
        <sal>1500.00</sal>
    </employee>
    <employee deptno="20" empno="7876" mgr="7788">
        <ename>ADAMS</ename>
        <sal>1100.00</sal>
    </employee>
    <employee deptno="30" empno="7900" mgr="7698">
        <ename>JAMES</ename>
        <sal>950.00</sal>
    </employee>
    <employee deptno="20" empno="7902" mgr="7566">
        <ename>FORD</ename>
        <sal>3000.00</sal>
        <comm>0.0</comm>
    </employee>
    <employee deptno="10" empno="7934" mgr="7782">
        <ename>MILLER</ename>
        <sal>1300.00</sal>
    </employee>

Result must be department with number 20 because sum by 20 dept = 10075 > 9550 (dept 10) > 9400 (dept 30).

Edit: New version XML document added

2) Yes, i want sum by dept 2 - 600+200=800, next by dept 3 - 400+500=900, maximum 900, and how get value dept 3? ( — Mike Z, Sep 25 '15 at 10:46
If you have an XML question, it helps if you supply valid XML. — Sobrique, Sep 25 '15 at 13:18

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

Maybe something like that :

/employee/sal[not(. < ../../sal)][1]

further examples can be found on this thread

edited May 23 '17 at 11:51

Community

1
1

answered Sep 25 '15 at 12:39

flafoux

2,080
1
12
13

what about this one ? `/employee/sal[not(. < ../../sal)][1]` – flafoux Sep 25 '15 at 12:52
i write in java-class) i use < )) – Mike Z Sep 25 '15 at 12:55
me need sum by each department, in this xpath-example not calculating by each department, in this example calculating minimum value in all node's – Mike Z Sep 25 '15 at 12:57
correct example MAX - //employee/sal[not(. < //employee/sal) correct example MIN - //employee/sal[not(. > //employee/sal) but that's not what I you need (( – Mike Z Sep 25 '15 at 13:05

score 0 · Answer 2 · answered Sep 25 '15 at 13:22

0

I don't think you can with xpath - I would probably break out perl:

#!/usr/bin/env perl
use strict;
use warnings;
use XML::Twig;

my %sum_of_depts;

my $twig = XML::Twig->parsefile('my_file.xml');
foreach my $employee ( $twig->findnodes('//employee') ) {
    my $deptid = $employee->att('deptno');
    my $sal    = $employee->first_child_text('sal');
    $sum_of_depts{$deptid} += $sal;
}

foreach my $deptid ( sort { $sum_of_depts{$b} <=> $sum_of_depts{$a} }
    keys %sum_of_depts )
{
    print "$deptid => $sum_of_depts{$deptid}\n";
}

Which given your sample input gives:

20 => 10075
10 => 9550
30 => 9400

answered Sep 25 '15 at 13:22

Sobrique

52,974
7
60
101

Fair enough. I understand it isn't quite what you're after - I'm just sort of hoping it's a useful enough _potential_ solution that others might find it does suit their needs. – Sobrique Sep 25 '15 at 13:28
@MikeZ As I said above, it's technically impossible to do this in XPath. XPath is not a programming language, it has certain limitations. This is one of them. You need a host language to fill in the missing parts that XPath can't do. – Tomalak Sep 25 '15 at 14:13

XPath find maximum sum by department (several departments)

2 Answers2