Home » SQL & PL/SQL » SQL & PL/SQL » Avg of the count ??
Avg of the count ?? [message #400158] Sun, 26 April 2009 14:15 Go to next message
marsguy85
Messages: 14
Registered: August 2007
Junior Member
Hello I am stuck at a query . Please help.

I need to find out avg (count) of times the book copies were borrowed . Then i need to find out number of times that particular copy is borrowed.

What i need in a table is like this :


Copy_id | hired_times | avg_times_book_hired |

212 | 2 | 4|

321 | 4 | 5 |

113 | 1 | 3 |


There are books which have multiple copies of them in the library.


Tables are :

loan = copy_id , evaluation , customer_id

copy = copy_id , book_id , location

book = book_id , title .





I tried but no cherry . I can get the total number of copies for book.

select c.book_id,count(l.copy_id) AS hirings
from copy c,loan l,book b
where c.copy_id = l.copy_id
and b.book_id = c.book_id
group by c.book_id

THIS GIVES me
book_id | hirings |
444 | 3|
445 | 4|



Re: Avg of the count ?? [message #400159 is a reply to message #400158] Sun, 26 April 2009 14:21 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You've been directed previously to the Posting Guidelines.
I'll try again, again.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


Post DDL for tables.
Post DML for test data.

Post expected/desired results

Please use <code tags> to make your posts readable.
Re: Avg of the count ?? [message #400161 is a reply to message #400158] Sun, 26 April 2009 15:08 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I previously pointed to OraFAQ Forum Guide in your previous topic you should now know how to post, otherwise please read it again.

For instance, you should know that you have to post a working Test case: create table and insert statements along with the result you want with these data.
Also always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: 'DBMS_XMLDOM' problem
Next Topic: generic data retention
Goto Forum:
  


Current Time: Sat Dec 03 06:16:15 CST 2016

Total time taken to generate the page: 0.06552 seconds