php - 计算联接中选择的结果

我有这个查询，可以根据需要工作。但是，我想有一列显示记录重复的次数。

查询：

SELECT total.Total，subqry。公司AS供应商，公司。公司AS客户，purchase_details.CompanyID，
                            product.Description1，purchase_details.PurchaseID，purchase_details.PurchaseCreated，
                            purchase_details.Completed，purchase_details.OriginalOrd
                    从purchase_details
                        LEFT JOIN product on Purchase_details.PartNumber = product.PartNumber
                        在purchase_details.OriginalOrd = order.OrderID上向左加入订单
                        LEFT JOIN联系ON订单.ContactLinkID = contact.ID
                        LEFT JOIN公司ON contact.LinkID = company.LinkID
                        LEFT JOIN（SELECT company.Company，Purchase_details.CompanyID FROM company
                                    LEFT JOIN Purchase_details ON company.ID = Purchase_details.CompanyID）
                                    AS subqry ON Purchase_details.CompanyID = subqry.CompanyID
                        左联接（选择OriginalOrd，计数（DISTINCT公司ID），AS总计，来自购买详细信息
                                    GROUP BY OriginalOrd）AS total on Purchase_details.OriginalOrd = total.OriginalOrd
                    在哪里Purchase_details.Completed = $ show AND
                        （subqry.Company喜欢：搜索
                        或company.Company喜欢：搜索
                        或product.Description1 LIKE：搜索
                        或Purchase_details.PartNumber喜欢：搜索
                        或Purchase_details.PurchaseID喜欢：搜索
                        或purchase_details.OriginalOrd Like：搜索）
                    按购买编号分组

公司ID列返回：

504
1739
504
1389
504

我想在联接中以理想的方式返回以下信息：

504 | 3
1739 | 1个
504 | 3
1389 | 1个
504 | 3


我将在php应用程序中使用此列来触发其他功能。

提前致谢

最佳答案

在PurchaseID之后将company_id添加到GROUP BY列表中，然后将另一个列计数（DISTINCT company_id）company_count添加到选择中。

SELECT
....//other columns
count(DISTINCT purchase_details.CompanyID) company_count
FROM
...//rest of queries
GROUP BY purchase_details.CompanyID,PurchaseID
;