mysql - 如何按相关记录的数量进行分组

Question

我有两张 table invoices和products 。

invoices: store, 
products: id, invoice_id

我想要一个结果集，显示每种产品数量有多少张发票。

我的意思是，如果我有 2 张发票，其中 A 商店各有 3 种产品，它将显示 Store: A, Products qty: 3, Number of invoices (with three products): 2

另一个例子:

| store | products_qty |    count   |
|   A   |      1       |      10    | 
|   A   |      2       |      7     |
|   A   |      5       |      12    |
|   B   |      5       |      12    |

这意味着，商店 A 有 10 张发票，其中包含 1 种产品。 7 个有 2 种产品，12 个有 5 种产品...

我尝试过类似的方法:

SELECT store, count(p.id), count(i.id) FROM invoices i
LEFT JOIN products p ON (p.invoice_id = i.id)
GROUP BY price, count(i.id)

但是我的团体原因无效，它显示 Invalid use of group function .

我怎样才能做到这一点？

Answer 1

我能够使用子查询，我想知道没有它是否可以:

SELECT store, products_qty, count(*) FROM (
   SELECT store, count(p.id) as products_qty, count(i.id) as invoices_count 
   FROM invoices i
   LEFT JOIN products p ON (p.invoice_id = i.id)
   GROUP BY price, i.id
) AS temp GROUP BY store, products_qty;