CODEWITHSUNDEEP
sqloracle-databasejoin
Alessandro
Alessandro

Reputation: 369

How to build a sql query to return avg(price), min(price), max(price) joined with avg(order), min(order), max(order)

I would like to create a sql query that returns results together for 2 different queries. For example, I'd like to have results in the form of: Product Name, avg(price), min(price), max(price), avg(order), min(order), max(order) At the moment I have 2 sql queries of the form:

select 
  product.name, order.id, avg(price), min(price), max(price) 
from 
  product, order 
where
  product.name = order.product_name and product.name = 'price' 
group by 
  product.name, order.id

select 
  product.name, order.id, avg(order), min(order), max(order) 
from 
  product, order 
where 
  product.name = order.product_name and product.name = 'order' 
group by 
  product.name, order.id

Some products have a price and an order, others have only a price, and others have only and order. How do I write a query that will display all the results and join those that have both an order and a price and display those that have both as i row?

Upvotes: 3

Views: 2883

Answers (2)

Eric Petroelje
Eric Petroelje

Reputation: 60518

I think what you need is a full outer join since rows could be in either one table or the other or both:

SELECT 
   NVL(t1.name,t2.name) as name,
   NVL(t1.id, t2.id) as id,
   avg_price,
   min_price,
   max_price,
   avg_order,
   min_order,
   max_order
FROM 
(select product.name, order.id, avg(price) as avg_price, min(price) as min_price, max(price) as max_price 
from product, order 
where product.name = order.product_name and product.name = 'price' 
group by product.name, order.id) t1
FULL OUTER JOIN
(select product.name, order.id, avg(order) as avg_order, min(order) as min_order, max(order) as max_order
from product, order 
where product.name = order.product_name and product.name = 'order' 
group by product.name, order.id) t2
ON t1.name = t2.name

Upvotes: 4

Allan
Allan

Reputation: 17429

Your queries don't make a whole lot of sense, so there's a lot I had to attempt to infer from your question. It's always better to post the create table scripts and some sample data (as well as the desired output) when asking SQL questions.

Below is a scenario I created that attempts to recreate your problem.

CREATE TABLE product(id NUMBER, name VARCHAR2(10));

CREATE TABLE orders(product_name VARCHAR2(10), VALUE NUMBER);

INSERT INTO product
VALUES     (1, 'order');    
INSERT INTO product
VALUES     (1, 'price');    
INSERT INTO product
VALUES     (2, 'order');    
INSERT INTO product
VALUES     (3, 'price');    
INSERT INTO orders
VALUES     ('order', 5);    
INSERT INTO orders
VALUES     ('price', 5);    
COMMIT;

Given this scenario, the following query will provide 3 rows, with the "order" and/or "price" columns populated where appropriate.

SELECT   p.id,
         AVG(o1.VALUE) as avg_price,
         MIN(o1.VALUE) as min_price,
         MAX(o1.VALUE) as max_price,
         AVG(o2.VALUE) as avg_order,
         MIN(o2.VALUE) as min_order,
         MAX(o2.VALUE) as max_order
FROM             product p
         LEFT JOIN orders o1
              ON p.name = o1.product_name AND p.name = 'price'
         LEFT JOIN orders o2
              ON p.name = o2.product_name AND p.name = 'order'
GROUP BY p.id

Upvotes: 0

Related Questions