使用分组依据语句的结果计算平均值

Question

我有一个包含两个表的数据库。

借款人表

BorrowID | ClientID | BookID

客户端表

ClientID | Occupation

借款人表中的客户端 ID 是外键。

我正在尝试按职业获取借阅书籍的平均数量。 我能够通过此查询获取每个职业的人数：

SELECT count(Occupation) as occupation_count, Occupation
FROM client
GROUP BY Occupation

我还可以通过此查询获取每个职业的书籍数量：

SELECT client.Occupation, count(borrower.ClientID) AS books_per_occupation
FROM client
LEFT JOIN borrower
ON (borrower.ClientID=client.ClientID)
GROUP BY client.Occupation

问题是我不能将books_per_occupation除以 occupation_count.
似乎子查询就是这样，但我似乎无法让它工作。

Answer 1

如果我理解正确，您希望每个客户的书籍数量按职业。 如果是这样，您可以使用单个查询并count(distinct)：

SELECT c.Occupation, count(b.ClientId) / count(distinct c.clientId) AS books_per_client
FROM client c LEFT JOIN
borrower b
ON b.ClientID = c.ClientID
GROUP BY c.Occupation;