Trying to average the results of a count which is grouped?-CodePudding

I am using MySQL and am trying to create a query to solve this question:

Average number of borrowed books by occupation

My plan was to count the number of instances of 'BorrowID' because each time a book is borrowed it creates a unique BorrowID. Then group those by clientID so that each person has their total listed books borrowed. Then this is where I start to get lost, as I obviously want to average all the grouped occupations however I am not sure if I am doing that...

First I tried:

SELECT client.Occupation, AVG(BorrowIDCount)
FROM
    (
    SELECT COUNT(BorrowID) as BorrowIDCount
    FROM client, borrower
    WHERE client.ClientID = borrower.ClientID
    GROUP BY borrower.ClientID
    ) as x
GROUP BY Occupation

But it gives the error:

Unknown column 'client.Occupation' in 'field list'

Which I thought was because the outer query needed to know which tables...

So then I tried:

SELECT client.Occupation, AVG(BorrowIDCount)
FROM client, borrower
WHERE client.ClientID = borrower.ClientID AND
    (
    SELECT COUNT(BorrowID) as BorrowIDCount
    FROM client, borrower
    WHERE client.ClientID = borrower.ClientID
    GROUP BY borrower.ClientID
)
GROUP BY Occupation

It didn't like the alias for the subquery so I removed it although no idea why, however it then gave this error:

Unknown column 'BorrowIDCount' in 'field list'

I feel like I may be completely off base in terms of how to create this query but I also feel that I might be close and am just not understanding some rules or syntax here. Any help in the right direction would be incredibly appreciated.

Thanks!

CodePudding user response：

It looks to me like you want to figure out the number of books borrowed by client, then to average that number by occupation. So let's do it in steps:

First is a subquery to get the books per client.

                    SELECT COUNT(*) borrowed, ClientID
                      FROM borrower
                     GROUP BY ClientID

Next, we use that subquery in an outer query to get the average you want.

SELECT AVG(byclient.borrowed) average_borrowed,
       client.Occupation
  FROM (
                    SELECT COUNT(*) borrowed, ClientID
                      FROM borrower
                     GROUP BY ClientID
       ) byclient
  LEFT JOIN client ON byclient.ClientID = client.ClientID
 GROUP BY client.Occupation
 ORDER BY AVG(byclient.borrowed) DESC, client.Occupation;

Your requirement calls for an aggregate of an aggregate, so you must nest one aggregate query inside another.

LEFT JOIN allows the inclusion of clients without any occupation. If you don't want that just use JOIN.

The first query in your question failed because your FROM clause referred to a subquery (a virtual table) that lacks the Occupation column. The second one failed because AND (virtual table) doesn't mean anything in SQL.

This nesting of virtual tables is the Structured part of Structured Query Language.