Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Aggregation stumper
A copy of this was sent to maxmike_at_ibx.bc.ca
(if that email address didn't require changing)
On Fri, 15 May 1998 16:17:21 GMT, you wrote:
>
>
>I believe the following is not directly expressible in a single query:
>
>"Customer names for customers that have at most 3 orders with the
>average of order amounts greater than 2000"
>
select customer
from orders
group by customer
having count(*) <= 3 and avg(order_amount) > 2000;
having is a seldom used clause that allows you to apply a 'where clause' to aggregate values.
>In other words I need the average of the counted orders.
>
>I'd love to be shot down in my belief! Any ideas?
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri May 15 1998 - 11:56:48 CDT