2

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?

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.