Home » SQL & PL/SQL » SQL & PL/SQL » Average from two tables
Average from two tables [message #210489] Thu, 21 December 2006 01:32 Go to next message
nids2307
Messages: 8
Registered: December 2006
Location: India
Junior Member
The database scheme consists of four relations:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

I need the Query for:
Find the average price of the PCs and laptops produced by maker A.
Result set: single total price.

Do I need to first find the sum and total count from both the tables and then get the average OR find average from both the tables through "avg" function and then structure the query?

Plz help
Re: Average from two tables [message #210493 is a reply to message #210489] Thu, 21 December 2006 01:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You should first get all the relevan rows from the two tables and THEN calculate the average using avg().
Let the database do the work and don't bother yourself with weighing the two averages Wink
icon7.gif  Re: Average from two tables [message #210496 is a reply to message #210493] Thu, 21 December 2006 01:58 Go to previous messageGo to next message
nids2307
Messages: 8
Registered: December 2006
Location: India
Junior Member
Can you plz frame the query for me?
Re: Average from two tables [message #210516 is a reply to message #210496] Thu, 21 December 2006 03:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Nope. You have to try it yourself first.
This site is NOT a query-generator. Consider it more like a corrector and tutor, not a 'could-you-please-do-my-(home)work-for-me-and-by-the-way-it-is-urgent' thing.
Re: Average from two tables [message #210545 is a reply to message #210516] Thu, 21 December 2006 04:52 Go to previous messageGo to next message
nids2307
Messages: 8
Registered: December 2006
Location: India
Junior Member
I very well know that this site is not a query generator,
I tried it myself but not getting the correct output Sad
Re: Average from two tables [message #210550 is a reply to message #210545] Thu, 21 December 2006 05:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
No problem. Show us what you tried and where it failed.
We will gladly help you further.
Re: Average from two tables [message #210558 is a reply to message #210550] Thu, 21 December 2006 05:27 Go to previous messageGo to next message
nids2307
Messages: 8
Registered: December 2006
Location: India
Junior Member
The following query will result in the relevant rows:

select price from pc,product where maker='a' and pc.model=product.model
union
select price from laptop,product where maker='a' and laptop.model=product.model


how should I use avg() with the result of the above query?
Re: Average from two tables [message #210559 is a reply to message #210558] Thu, 21 December 2006 05:34 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use union all to avoid that duplicate rows get filtered.

Now use this query in the from-clause (where you normally would put a tablename) like this:

select avg(???)
from (select price from pc,product where maker='a' and pc.model=product.model
union all
select price from laptop,product where maker='a' and laptop.model=product.mode)


Please let us know the results

[Edit: forgot to change the union all myself Wink]

[Updated on: Thu, 21 December 2006 05:34]

Report message to a moderator

Re: Average from two tables [message #210562 is a reply to message #210559] Thu, 21 December 2006 05:49 Go to previous messageGo to next message
nids2307
Messages: 8
Registered: December 2006
Location: India
Junior Member
The Query still remains the same , what needs to be keyed in with avg(???)
Re: Average from two tables [message #210569 is a reply to message #210562] Thu, 21 December 2006 06:20 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Again: try something and show us
Previous Topic: about materialised view
Next Topic: Listener.ora
Goto Forum:
  


Current Time: Wed Dec 07 20:03:52 CST 2016

Total time taken to generate the page: 0.07948 seconds