Group By and Count in Xquery

Question

I am trying to count Similar 'itemid_ref', to get numbers of each Items ref in this: ``` 1 2 2 20000 2 3 3 250000 3 3 3 30000 4 1 2 140000 5 2 1 10000 ``` I am trying to get the output from xquery to be like this: ``` 2 2

Accepted Answer

There's no `group by` in XQuery 1.0, but usually you can get by using `distinct-values`, as long as the data set isn't too large.

```
for $itemid_ref in distinct-values(/electrystore/itembill/itemid_ref)
let $count := count(/electrystore/itembill[itemid_ref = $itemid_ref])
return element result {
  element itemid_ref { $itemid_ref },
  element numberOfitemes { $count }
}
```

To be performant on large sets of data, you'll probably need to look into extensions available within your XQuery processor that allow access to indexes.

In XQuery 3.0 it's a little simpler:

```
for $itembill in /electrystore/itembill
let $itemid_ref := $itembill/itemid_ref
group by $itemid_ref
return element result {
  element itemid_ref { $itemid_ref },
  element numberOfitemes { count($itembill) }
}
```

Group By and Count in Xquery

Solution

Problem