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
inarobis@gmail.com wrote:
Quote:
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
|
Simple group by with Xquery
