CODEWITHSUNDEEP
phpmysql
David Mukoro
David Mukoro

Reputation: 467

Select, Group and Sum Result from Database using mysqli

I have a record with customerId, GroupId, amountContributed which is captured on a daily basis.Now I want to return a table showing the total amount contributed by a customer with the group id.My previous code is:

if(isset($_POST["searchVal"])){
//$searchText = $_POST["searchText"];
$sDate = $_POST["sDate"];
$eDate = $_POST["eDate"];
$query = mysqli_query($connection,"SELECT custid,grpid,amountContribute FROM tab_customer_dailycontribution WHERE cast(transactionDate as date) BETWEEN '$sDate' AND '$eDate'");
if($connection->affected_rows>0){
    $c =0;$con =0.00;

while($rw = mysqli_fetch_array($query)){
    $c++;?> 
    <tr>
    <td><?php echo $c;?></td>
    <td><?php echo $rw['custid'];?></td>
    <td><?php echo $rw['grpid'];?></td>
    <?php $con+=$rw['amountContribute']; ?>
    <td><?php echo number_format($con,2);?></td>
  <?php  } ;?>
   </tr>
   <tr>

The above give this table:

S/N CustomerID  Group ID    Total Contribution
    1   SC20151 SGC20151    700.00
    2   SC20151 SGC20151    500.00
    3   SC20151 SGC20151    500.00
    4   SC20152 SGC20151    500.00
    5   SC20152 SGC20151    500.00
    6   SC20152 SGC20151    500.00
    7   SC20152 SGC20151    500.00
    8   SC20152 SGC20151    500.00
    9   SC20152 SGC20151    500.00
    10  SC20152 SGC20151    500.00
    11  SC20152 SGC20151    500.00
    12  SC20152 SGC20151    500.00
    13  SC20152 SGC20151    500.00
    14  SC20153 SGC20152    1,000.00
    15  SC20153 SGC20152    1,000.00

So, i want the ouput to be like this table below:

 SN        Customer ID      Group ID       Amount Conributed
    1         SC20152           SCG20151        5000
    2         SC20151           SCG20151        1200
    3         SC20153           SCG20152        2000

Can someone help. I have used group by customerid,amountcontributed but not working.

Upvotes: 0

Views: 587

Answers (1)

syck
syck

Reputation: 3029

SELECT custid, grpid, SUM(amountContribute) AS sumAmountContribute
FROM tab_customer_dailycontribution 
WHERE CAST(transactionDate AS date) BETWEEN '$sDate' AND '$eDate'"
GROUP BY custid, grpid

will give you what you need.

When outputting, you will then have to change your array index:

<?php echo $rw['sumAmountContribute']; ?>

Upvotes: 1

Related Questions