How to average count column but by specific occupations

Question

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

Occupation	Avg of occupation
Bus Driver	4 (14/4)
Cashier	5
Computer Engineer	3 (6/2)
Computer Programmer	5.666 (17/3)

Answer 1

First aggregate only in the table Borrower to get the counts and then join to Client to get the average per Occupation:

SELECT c.Occupation, AVG(b.counter) average_count
FROM Client c
INNER JOIN (
  SELECT ClientId, COUNT(*) counter
  FROM Borrower
  GROUP BY ClientId
) b ON b.ClientId = c.ClientId
GROUP BY c.Occupation;