Simple group by with Xquery

[inarobis]

Hello,

I am newbie in Xquery and i would like to do a simple group by date and
ID.

so a piece of my xml file.
<Authors>
<Author>
<ID>1</ID>
<Date>30.07.2000</Date>
<NTS>56</NTS>
</Author>
<Author>
<ID>1</ID>
<Date>30.06.2000</Date>
<AI>0.00707</AI>
</Author>
<Author>
<ID>1</ID>
<Date>31.07.2000</Date>
<AI>0.00633</AI>
</Author>
<ID>1</ID>
<Date>31.05.2000</Date>
<TVS>2.33</TVS>
</Author>
<ID>1</ID>
<Date>30.06.2000</Date>
<TVS>2.54</TVS>
</Author>
<ID>1</ID>
<Date>31.07.2000</Date>
<TVS>2.46</TVS>
</Author>
....
</Authors>

I would like to group by date and author ID have the TVS, AI and NTS
for every date instead of having seperate records for each date

For example:

<Author>
<ID>1</ID>
<Date>30.07.2000</Date>
<NTS>56</NTS>
<AI>0.00633</AI>
<TVS>2.46</TVS></Author>

I tried to do several codes without success. Any suggestion for this
problem?

Ina

[inarobis]

For info this my xquey stuff

<TEST>
{
for $i in doc("authors.xml")//Info/Auhors/Author
for $date in $i/Date
let $nts:= fn:sum($i/NTS)
let $ai := fn:sum($i/AI)
let $tvs := fn:sum($i/TVS)

return
<Test>
{ $i/ID }
{ $date }
<NTS>{ $nts }</NTS>
<AI>{ $ai }</AI>
<TVS>{ $tvs }</TVS>
</Test>
}
</TEST>

I do no know how to do that in sql

select Date, ID, sum(isnull(NTS,0)) NTS, sum( isnull(AI,0)) AI,
sum(isnull(TVS,0) TVS
from Author
group by Date, ID

in******@gmail.com wrote:

Hello,

I am newbie in Xquery and i would like to do a simple group by date and
ID.

so a piece of my xml file.
<Authors>
<Author>
<ID>1</ID>
<Date>30.07.2000</Date>
<NTS>56</NTS>
</Author>
<Author>
<ID>1</ID>
<Date>30.06.2000</Date>
<AI>0.00707</AI>
</Author>
<Author>
<ID>1</ID>
<Date>31.07.2000</Date>
<AI>0.00633</AI>
</Author>
<ID>1</ID>
<Date>31.05.2000</Date>
<TVS>2.33</TVS>
</Author>
<ID>1</ID>
<Date>30.06.2000</Date>
<TVS>2.54</TVS>
</Author>
<ID>1</ID>
<Date>31.07.2000</Date>
<TVS>2.46</TVS>
</Author>
...
</Authors>

I would like to group by date and author ID have the TVS, AI and NTS
for every date instead of having seperate records for each date

For example:

<Author>
<ID>1</ID>
<Date>30.07.2000</Date>
<NTS>56</NTS>
<AI>0.00633</AI>
<TVS>2.46</TVS></Author>

I tried to do several codes without success. Any suggestion for this
problem?

Ina