mysql - 对平均函数进行 SQL 查询

Question

我正在尝试编写一个 SQL 查询来列出发票数量超过每个供应商平均发票数量的供应商。到目前为止，我提出了两个问题，但我不确定其中一个是否正确。如果有人可以帮助我。

SELECT VENDOR_ID,COUNT(INVOICE_ID), ROUND(AVG(INVOICE_ID), 2)
FROM INVOICES
GROUP BY VENDOR_ID
HAVING COUNT(INVOICE_ID) > AVG(INVOICE_ID)
ORDER BY AVG(INVOICE_ID);

SELECT VENDOR_ID,COUNT(INVOICE_ID), ROUND(AVG(INVOICE_ID), 2)
FROM INVOICES
GROUP BY VENDOR_ID
HAVING AVG(INVOICE_ID) > (SELECT AVG(INVOICE_ID) FROM INVOICES)
ORDER BY AVG(INVOICE_ID);

Answer 1

** 根据 Arth 的评论编辑

因此每个供应商的平均发票数量为:

SELECT cast(COUNT(Invoice_ID) as float) / COUNT(DISTINCT Vendor_Id)
FROM INVOICES

现在让我们将其放入您的需求中

SELECT Vendor_Id, COUNT(Invoice_Id)
FROM INVOICES
GROUP BY Vendor_Id
HAVING COUNT(Invoice_ID) > 
              (SELECT cast(COUNT(Invoice_ID) as float) / COUNT(DISTINCT Vendor_Id) FROM INVOICES)