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

database-designoraclesubquery

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?

Best Answer

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;

Related Solutions

Number field in varchar2 field

Not sure, why this is marked "Active yesterday". Once I'm here…

If it needs to be done (which it shouldn't, if proper database design can be applied), this could be a way:

SELECT
  Users.userID
  , Users.name AS userName
  , Groups.uids
  , Groups.name AS groupName
FROM Users
INNER JOIN Groups
  ON INSTR(TRANSLATE(Groups.uids, '()', ',,'), ',' || TO_CHAR(Users.userid) || ',') > 0
;

It turns the parentheses around the list of group ids into commas to simplify the comparison against the comma padded userIDs. The latter is to prevent, e.g., 123 to match 12.

See it in action: SQL Fiddle

Please comment, if and as this requires adjustment / further detail.

Mysql – Storing prices with price history and different specificity (global, user group, single user) in MySQL database

There are a couple of ways you could separate the current values from the historic ones: you could simply include a boolean field that is true for the latest price and filter on that, or you could keep current prices in a separate table. Both options involve a little extra work to maintain integrity but would make a query for current prices more efficient. This doesn't remove the need for the sub-query to pick the first row that matches the user/group/NULL condition, though fewer rows will be scanned in processing both the inner and outer queries.

The following might be more efficient as a straight query if only the current prices are in the base table (add the is_latest=true check or your current date filter to the where and on clauses if you keep the historic values in the same table):

SELECT CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field1 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field1 ELSE nullmatch.field1 END AS field1
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field2 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field2 ELSE nullmatch.field2 END AS field2
     (... and so on ...)
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.fieldN WHEN groupmatch.id IS NOT NULL THEN groupmatch.fieldN ELSE nullmatch.fieldN END AS fieldN
FROM   base_prices AS nullmatch
LEFT OUTER JOIN 
       base_prices AS groupmatch ON groupmatch.id=nullmatch.id AND nullmatch.referent_id = @GroupIDHere
LEFT OUTER JOIN 
       base_prices AS usermatch ON usermatch.id=nullmatch.id AND nullmatch.referent_id = @UserIDHere
WHERE nullmatch.referent_id IS NULL

This will only work if you always have a row for each product where referent_id is null, otherwise you could try:

SELECT CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field1 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field1 ELSE nullmatch.field1 END AS field1
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field2 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field2 ELSE nullmatch.field2 END AS field2
     (... and so on ...)
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.fieldN WHEN groupmatch.id IS NOT NULL THEN groupmatch.fieldN ELSE nullmatch.fieldN END AS fieldN
FROM   users
CROSS JOIN 
       base_prices AS nullmatch
LEFT OUTER JOIN 
       base_prices AS groupmatch ON groupmatch.id=nullmatch.id AND nullmatch.referent_id = users.groupid
LEFT OUTER JOIN 
       base_prices AS usermatch ON usermatch.id=nullmatch.id AND nullmatch.referent_id = users.id
WHERE users.id = @UserIDHere
AND   nullmatch.referent_id IS NULL

In both cases be careful using this construct in a view because further filtering by the fields brought out via those case statements will not be able to use any indexes you may have defined.

Obviously the case statement per field is a bit of faf but it should stop the query runner searching the table once for every row returned as it might with the sub-query arrangement.

In other databases you might be able to use windowing functions (like ROW_NUMBER) to do this more conveniently and efficiently, but mySQL does not support those to my knowledge.

Best Answer

Related Solutions

Number field in varchar2 field

Mysql – Storing prices with price history and different specificity (global, user group, single user) in MySQL database

Related Question