Using sub-queries in expressions
In Data Shaping Query Language , you can use a query inside an expression.
Any expression used in a query can contain a sub-query. These are supported in any clause that supports expressions, as long as the value returned is supported in the expression.
In the example below, the comparison expression in the HAVING clause
expects two numeric values, therefore the result of the sub-query used in this expression
must be a numeric value.
FROM order
UNNEST items
GROUP BY orderno
HAVING sum(qty * price) > ( FROM order UNNEST items SELECT avg(qty * price) )
SELECT {
orderno,
revenue = sum(qty * price)
} ORDER BY orderno