SQL – request to find the average loans by user

I am struggling with a SQL request.

I need to find a request to calculate the average loans per user. I was thinking in doing a LEFT OUTER JOIN between the table SUSCRIBER and the table Loan in order to get all the subscribers even if they have made a loan or not.

Then I have used a GROUP BY based on the IDNumber of the suscribers to COUNT the number of lines. Then I need to get the total numbers of suscribers to calculate the average but by doing this I get 1 or 0 only instead of an average. I don’t know what is wrong in this request. Maybe as I have made a group by the IDNumber from the table Loan. How could I get the number of all IDNumber based on this request to make the division ? Thanks

See, the request written in English:

SELECT COUNT(Loan.IDNumber) / COUNT(SUSCRIBER.IDNumber), SUSCRIBER.Name AS Name
FROM SUSCRIBER
LEFT OUTER JOIN Loan
ON SUSCRIBER.IDNumber = Loan.IDNumber
GROUP BY Loan.IDNumber;

Here are the tables:

TOPIC(Code_topic, Description)

KEY_WORD(Code_key_word, keyword)

PUBLISHER(Code_publisher, Name, Adress)

AUTHOR(Code_author, Name, Surname)

BOOK(Code_catalogue, Title, #Code_topic)

COPY (Code_bookshelf, Code_wear, Date_aquisition, #Code_publisher, #Code_catalogue)

SUSCRIBER(IDNumber, Name, Adress, Phone, Birthdate, Subscription_date)

Loan(#IDNumber,Code_bookshelf,Loan_date, Return_date)

Thank you in advance

Answer

I need to find a request to calculate the average loans per user.

If you want the average number of loans per subscriber, then you want the total number of loans divided by the number of subscribers. You can do this with an expression like this:

SELECT num_l * 1.0 / num_s
FROM (SELECT COUNT(*) as num_s FROM SUBCRIBER) s CROSS JOIN
     (SELECT COUNT(*) as num_l FROM Loan) l;

The * 1.0 is because some databases do integer division, so 3 / 2 = 1 rather than 1.5.

Advertisement

Answer