Subject:Xquery find avg and count Author:sap page Date:29 May 2006 08:23 AM Originally Posted: 29 May 2006 08:05 AM
I currently have a bit of a problem with my uni assignment and was wondering if anyone could help. This was about the only xquery site i could find a forum on.
Write a query to find all products whose sale price is more than twice the average price of some other product within the same type. Display the total number of products with such high prices for each type. Also display the name and price of these products. For each type, the product must be listed in increasing order by price.
Subject:Xquery Author:sap page Date:29 May 2006 08:10 AM
for $products in distinct-values(doc("products.xml")/products/product/@type)
return
for $type in doc("products.xml")/products/product[@type=$products]
let $avg_price := doc("products.xml")/products/product[@type=$type/@type]/price/text()
let $avg := doc("products.xml")/products/product[@type=$products]
for $t in $avg/price/text()
where avg($t)*2 < $type/price/text()
return count($t)
This is what I have so far just so you dont think i am using the forums to get free answers!
Subject:Xquery Author:Ivan Pedruzzi Date:29 May 2006 10:38 PM
Is something like the following works for you?
for $type in distinct-values(doc("products.xml")/products/product/@type)
let $products_by_type := doc("products.xml")/products/product[@type=$type]
let $products_count := count($products_by_type)
let $price_average := avg($products_by_type/price/text())
let $expensive_products := $products_by_type[price/text() > $price_average * 2]
return
(
count($expensive_products),
for $product in $expensive_products
return $product/name/text()
)