I need to show every book whose reader's age is below the mean age. The schema consists of these tables:
AUTHOR: ISBN, Name, Lastname;
BOOK: ISBN, Name, Publisher, Pages, Value, Year;
EXEMPLAR: Number, Taken (date), Returned (date), ISBN, Reader (reader's number), READER: Number (also reader's number, just named differently), ID number, Name, Lastname, Birthdate, Address.
I wrote the following code:
WITH Name_and_age (Age, Name, ISBN) as (SELECT age(birthdate) as Age, Book.ISBN, Name
FROM Reader
JOIN Exemplar on Reader.Number=Exemplar.Reader JOIN Book ON Book.ISBN=Exemplar.ISBN),
MeanAge (Average)
as (SELECT AVG(Age) as MeanAge FROM Name_and_age)
SELECT Age, MeanAge, Name, ISBN FROM Name_and_age, MeanAge
WHERE Age < MeanAge
But for some books the result displays 2 or 3 different age averages. The total number of unique books is 7, however the result gives me 10 rows. Any ideas? Thank you in advance!
You say, I need to show every book whose reader's age is below the mean age.
Your code includes this:
SELECT Age, MeanAge, Name, ISBN
You didn't say you wanted ages. If that's the case, don't select it. Also, use the word distinct to take into account books being read by more than one person. In other words, change the code above to this:
SELECT distinct MeanAge, Name, ISBN
You may also want to change this:
SELECT AVG(Age) as MeanAge
to this
SELECT floor(AVG(Age)) as MeanAge
That will cast the MeanAge to an integer and will ensure that if the average age was 15.5, 15 year old people will be excluded from the results.