在1个查询中求和列或使用PHP？

Question

我想我的问题是我试图在同一查询中求和计数，这可以在php中实现以避免这种情况吗？Records列显示记录的计数，而成本和成本合计，这很有效，但我想将这两列的总和显示出来，以显示总计？

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

最后使用With RollUp。表的单次扫描，所以它是面向性能的。

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
WITH RollUP

Refer here for more details

Answer 2

在MySQL中，如果不添加另一个查询并对它们执行UNION，就无法做到这一点，例如：

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中计算总计可能会有更好的性能，可能是在迭代行以构建表的时候。

Answer 3

如果要在PHP中循环结果集以进行显示，则可以在循环中对列求和。

例如

$sql = "your SQL here";

$query = mysql_query($sql);

$sum_records = $sum_costs = 0;

while ($r = mysql_fetch_object($query))
{
    // your other code here for whatever you're doing with the results
    $sum_records += $r->Records;
    $sum_costs += $r->Costs; 
}

// Now you have the two totals in $sum_records and $sum_costs.