php - 在 PHP 和 MYSQL 上的同一行上选择 SUM 和 COUNT

Question

我有两个表，客户和交易，我试图在同一行上显示来自同一客户的所有交易的 SUM 和 COUNT，并且交易类型，但我不知道如何对其进行分组，因此它仅显示一个客户端的总计，并在下一行显示另一个客户端的总计，而无需重复。感谢您的时间。

table clients

id_client - client_name

table transacctions

id_transaction - client_id - date - type - amount

   $query = " SELECT * FROM  transacctions, clients
    WHERE (transacctions.date BETWEEN '$date1' AND '$date2') 
    AND transactions.client_id = clients.id_client 
    AND transactions.type = '$type' ; "


     <th>Client Name</th>
     <th>Transaction Type</th>
     <th>Number of Transactions</th>
     <th>total amount</th>


       $output.='<td>'.$row['client_name'].'</td>';
       $output.='<td>'.$row['type'].'</td>';
       $output.='<td>'.$row['SUM(amount)'].'</td>';
       $output.='<td>'.$row['COUNT(amount)'].'</td>';

Answer 1

按客户ID分组并选择交易ID的金额和计数之和:

$query = " SELECT clients.client_name, transacctions.type
SUM(transacctions.amount) AS sum_amount,
COUNT(transacctions.id_transaction) AS transaction_count
FROM  transacctions, clients
WHERE (transacctions.date BETWEEN '$date1' AND '$date2') 
AND transactions.client_id = clients.id_client 
AND transactions.type = '$type' 
GROUP BY clients.id_client; "

或者，建议使用联接。这将为您提供相同的结果:

$query = " SELECT clients.client_name, transacctions.type
SUM(transacctions.amount) AS sum_amount,
COUNT(transacctions.id_transaction) AS transaction_count
FROM  clients
INNER JOIN transacctions ON transactions.client_id = clients.id_client 
WHERE (transacctions.date BETWEEN '$date1' AND '$date2') 
AND transactions.type = '$type' 
GROUP BY clients.id_client; "

如果您想查看所有客户端，包括没有交易的客户端，请将 INNER JOIN 更改为 LEFT JOIN:

$query = " SELECT clients.client_name, transacctions.type
SUM(transacctions.amount) AS sum_amount,
COUNT(transacctions.id_transaction) AS transaction_count
FROM  clients
LEFT JOIN transacctions ON transactions.client_id = clients.id_client 
WHERE (transacctions.date BETWEEN '$date1' AND '$date2') 
AND transactions.type = '$type' 
GROUP BY clients.id_client; "

注意:我没有修复您的查询中“transaction”的拼写错误，因为我不确定这是一个拼写错误还是您的表实际上有这个名称。