php - 在 1 个查询中对 Sum & Count 列求和或使用 PHP？

Question

我想我的问题是我试图在同一个查询中求和一个计数，这可以在 php 中实现以避免这种情况吗？ Records 列显示记录数，而 Costs 汇总成本，这很好用，但我想对这两个列求和以显示总计？

Select
  tblcontacts.ContactFullName As Name,
  Count(tblcases.CaseID)As Records,
  Sum(tblcases.CaseCommission) As Costs
From
  tblcontacts Inner Join
  tblcases On tblcontacts.ContactID =
    tblcases.ContactAssignedTo
Group By
  tblcontacts.ContactFullName

Answer 1

如果不添加另一个查询并对它们执行 UNION，则不能在 MySQL 中执行此操作，例如:

Select
  tblcontacts.ContactFullName As Name,
  Count(tblcases.CaseID)As Records,
  Sum(tblcases.CaseCommission) As Costs
From
  tblcontacts Inner Join
  tblcases On tblcontacts.ContactID =
    tblcases.ContactAssignedTo
Group By
  tblcontacts.ContactFullName

UNION ALL

Select
  '--GRAND-TOTAL--',
  Count(tblcases.CaseID),
  Sum(tblcases.CaseCommission)
From
  tblcontacts Inner Join
  tblcases On tblcontacts.ContactID =
    tblcases.ContactAssignedTo

但是，在这种情况下，您可能会通过 PHP 计算总计获得更好的性能，可能同时遍历行以构建表。