Calculated field on number of rows

Other Tech Topics
1

Hi all , i'll show you my problem.
Here are the table:

Book (Name,Author,Library_holding)
Library (Name,City)

Now , Library_holding is a number that tells how many libraries have that book.
I want to make a query between these two tables that has this structure

Query(Book_name,Book_author,Library_holding,average)

Average for a book should be the division of how many libraries hold that book compared to all libraries.
If in the database i have 20 libraries memorized and Book1 is present in 3 libraries average should be 3/20. How can i do this? Thanks in advance

2

try this

declare @LibraryCount int;
Select @LibraryCount = COUNT(Name) FROM Library

SELECT
Name BOOK_nAME,
Author BOOK_Author,
Library_holding,
Library_holding/@LibraryCount average
from

Book
3

Hi, thanks for replying. I forgot to say i'm working with Access but anyway i work with the SQL view of queries. It tells me declare is not recognized for a query, only select,delete,insert,update,procedure.

4

I am not sure Please try this

SELECT
Name as BOOK_nAME,
Author as BOOK_Author,
Library_holding,
Library_holding/(Select COUNT(Name) FROM Library) as average
from
Book

1 Like
5

You saved me , really thanks , it worked to put the select clause inside the division.