AS clause in oracle 11g XE

Question

tables: Primary keys have been highlighted

customer(customer_id,f_name,l_name,tel_no,addr,nic,city)
product(product_id,name,description,price,sales_tax)
branch(branch_id,name,tel_no,addr,city)
orders(order_id,customer_id,order_date,branch_id)
order_product(order_id,product_id,quantity)

This is a very simplified version of my query:

SELECT customer_id, SUM(quantity) AS avg_quantity
FROM orders,order_product
WHERE orders.order_id = order_product.order_id
GROUP BY customer_id
HAVING SUM(quantity) > (SELECT AVG(no_quantity)
FROM(
  SELECT customer_id, SUM(quantity) AS no_quantity
  FROM orders,order_product
  WHERE orders.order_id = order_product.order_id
  GROUP BY customer_id))

I have 2 sub-queries, i want to store the resultset temporarily while query execution. i have seen SQL example that do this :

(SELECT * FROM orders WHERE branch_id = 4) AS tempone

I want to store the resultset so i can use it in upper level of sub query.The above query does not run in oracle sql developer.

ERROR : ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"

I don't want to use temporary tables. I've already tried WITH AS clause, the query takes too long to execute, as in more than 20 mins. I dont know why

DazzaL DazzaL · Accepted Answer · 2012-12-19T14:57:12

you should be able to simplify this with:

select customer_id, avg_quantity
  from (select customer_id,
               sum(quantity) as avg_quantity,
               avg(sum(quantity)) over () global_avg_quantity
          from orders,
               order_product
         where orders.order_id = order_product.order_id
         group by customer_id)
 where avg_quantity > global_avg_quantity;

but i see you've put SUM(quantity) AS avg_quantity sum of quantity = average?

AS clause in oracle 11g XE

1 Answers