Outputting the results of two select queries on to a single table in Oracle SQL

Question

So after constructing a database for an "online auction website" I need to write a query that takes the average price of two different types of products and displays them in a table with the name of the product type next to it EG:

Gender   Averageprice
Mens     £65.34
Womens   £86.42

The price of the items and the gender of the clothing are in two seperate tables as a parent child relationship ie clothing is a subtype of item.

DESC TABLE ITEM
Name    Null     Type         
------- -------- ------------ 
ITEM_ID NOT NULL NUMBER(38)   
PRICE   NOT NULL NUMBER(12,6)

DESC TABLE ITEM_CLOTHING
Name         Null     Type         
------------ -------- ------------ 
ITEM_ID      NOT NULL NUMBER(38)   
CLOTHES_TYPE NOT NULL VARCHAR2(10)

I can list the names of the clothing types using...

SELECT DISTINCT CLOTHES_TYPE
FROM ITEM_CLOTHING;

and I can list the average price of the two different clothing types using...

select sum(PRICE) / count(*)
FROM ITEM,ITEM_CLOTHING
WHERE ITEM.ITEM_ID = ITEM_CLOTHING.ITEM_ID AND ITEM_CLOTHING.CLOTHES_TYPE = 'Mens'
UNION ALL 
select sum(PRICE) / count(*)
FROM ITEM,ITEM_CLOTHING
WHERE ITEM.ITEM_ID = ITEM_CLOTHING.ITEM_ID AND ITEM_CLOTHING.CLOTHES_TYPE = 'Womens';

I just can't work out how to put it all together in to a single table as shown above. Could you please help?

Answer 1

Look into GROUP BY queries.

SELECT gender, sum(PRICE) / count(*)
FROM ITEM, ITEM_CLOTHING 
WHERE ITEM.ITEM_ID = ITEM_CLOTHING.ITEM_ID
GROUP BY gender;

Or to refactor further:

SELECT gender, AVG(Price) AveragePrice
FROM ITEM
JOIN ITEM_CLOTHING ON ITEM.ITEM_ID = ITEM_CLOTHING.ITEM_ID
GROUP BY gender;