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

CodePudding user response：

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;

CodePudding user response：

The solution is pretty simple:

SELECT Occupation, count(*) BooksBorrowed
FROM Borrower
NATURAL LEFT JOIN Clients
GROUP BY Occupation;

SQL fiddle

You just need to join tables and calculate count with group by occupation.

CodePudding user response：

If you want to find the average number of borrowed books from all occupations, then use:

SELECT AVG(num_books)
FROM 
(
    SELECT COUNT(b.ClientId) AS num_books
    FROM client c
    LEFT JOIN borrower b ON b.ClientId = c.ClientId
    GROUP c.Occupation
) t;

The idea here is that each record in the intermediate subquery aliased as t above corresponds to the book count for a certain occupation. We then wrap that query and just take the average of all the counts to get the overall average book count.

CodePudding user response：

INNER JOIN will help finding the average count

SELECT Occupation as Jobtitle, count(*) as BooksBorrowed 
FROM Borrower 
NATURAL INNER JOIN Clients 
GROUP BY Occupation;