I’m currently doing an assignment for a SQL class and can’t seem to figure out how to go on from this. Going on from what I have I can’t seem to average the counts per occupation. With what I have so far I see how many books each person borrowed. If I try to average the column it just averages everything together when I need it specifically by occupation. Also, I Grouped by clientId because each client has multiple books borrowed.
The assignment is The average number of borrowed books by job title.
Borrower Table:
BorrowId(PK), ClientId(FK), BookId, BorrowDate
Client Table:
ClientId(PK), ClientFirstName, ClientLastName, ClientDoB, Occupation
This is what I have so far.
SELECT **, count(*)
FROM borrower
INNER JOIN client on borrower.ClientId = client.ClientId
GROUP BY borrower.ClientId
Here is what I get from the query that I have so far. My goal is to average these counts by occupation. So, for example, I want it to show
Bus Driver | 4 (14/4) Cashier | 5 Computer Engineer | 3 (6/2) Computer Programmer | 5.666 (17/3)