Select SUM and COUNT on same row on PHP and MYSQL

Question

I have two tables, clients and transactions, I´m trying to show on the same row the SUM and COUNT of all the transactions from the same client, and type of transactions, but I don't know how to group it so it only shows the totals of one client and on the next row the total for another client withouth repeting it. Thank you for your time.

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>';

You have "transactions" spelled wrong in a few places and correct in one place. — Difster
– Difster, Commented Jul 21, 2017 at 22:04

Racil Hilan · Accepted Answer · 2017-07-22 02:00:48Z

1

Group by the client ID and select the sum of the amount and the count of the transaction 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; "

Alternatively, it is recommended to use joins. This will give you the same results:

$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; "

And if you want to see all clients, including the ones who don't have transactions, then change INNER JOIN to 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; "

Note: I didn't fix the misspelling of "transaction" in your query, because I was not sure if it was a typo or your table actually has that name.

edited Jul 22, 2017 at 2:00

answered Jul 21, 2017 at 22:09

Racil Hilan

25.4k13 gold badges56 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raychu150 Over a year ago

Thanks, it does bring the SUM and COUNT, but only those values, do you know how to bring the "client_name" and transaction "type" on the same row also?.

Racil Hilan Over a year ago

Just add them to the SELECT. See my updated answer.

Collectives™ on Stack Overflow

Select SUM and COUNT on same row on PHP and MYSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related