Summing all column in MySQL table and perform some operation on the results

[Ponel]

Good day sirs, Please help me on the challenges I'm facing.. 

I have two tables "transaction_tbl" and "referal_tbl". What I wanted to do is to check the 

Transaction_tbl

 

 

referal_tbl

 

this my code

select transaction_tbl.amount_deposited, transaction_tbl.userID trans_userID, referal_tbl.userID ref_userID, referal_tbl.referalID ref_referalID from transaction_tbl, referal_tbl where referal_tbl.referalID='1' and transaction_tbl.userID=referal_tbl.userID GROUP by transaction_tbl.userID

 

My Output

 

I wanted to sum amount_deposited column so i will get 1,240 and get the 5% of 1240 which is 62

My Query

  $sql_referrals_bonus = mysqli_query($con, "select transaction_tbl.amount_deposited, transaction_tbl.userID trans_userID, referal_tbl.userID ref_userID, referal_tbl.referalID ref_referalID from transaction_tbl, referal_tbl where referal_tbl.referalID='$user[id]' and transaction_tbl.userID=referal_tbl.userID GROUP by transaction_tbl.userID");
        
        
        
        $result_row_referral = mysqli_num_rows($sql_referrals_bonus);
        $row_fetch= mysqli_fetch_row($sql_referrals_bonus);
        
      if ($result_row_referral>0){
        
        foreach($sql_referrals_bonus as $sub_referral){
            
             //$total_bonus += $row_fetch[0];
             //$total_bonus = $row_fetch[0];
              $total_bonus = $sub_referral["amount_deposited"];
             $total_bonus = "$".(0.05 * $total_bonus);
             //$total_bonus = "$".number_format("$total_bonus", 0, '', ',').".00";
        }
        
             
       
    }else{
             $total_bonus = "$"."0".".00";
    }   

 

 

so i got 30 instead of 62

Note the 30 is the 5% of 600.

 

Please any help on this sirs?

 

[Barand]

In each iteration of your foreach() loop you are calculating 5% of that row's bonus. You are not accumulating any totals.

You need to accumulate the total bonus in the loop then, after the loop, calculate 5% of the total..

Or you could do all the calculation in the query. I'd show you how but pictures are totallt useless for recreating test data.

[Ponel]

Ok sir. I will check that out and post my output. Thanks 🙏🙏

 

[Ponel]

Thanks it worked 

My Query

 

  $sql_referrals_bonus = mysqli_query($con, "select transaction_tbl.amount_deposited, transaction_tbl.userID trans_userID, referal_tbl.userID ref_userID, referal_tbl.referalID ref_referalID from transaction_tbl, referal_tbl where referal_tbl.referalID='$user[id]' and transaction_tbl.userID=referal_tbl.userID GROUP by transaction_tbl.userID");

        

        

        

        $result_row_referral = mysqli_num_rows($sql_referrals_bonus);

        $row_fetch= mysqli_fetch_row($sql_referrals_bonus);

        

      if ($result_row_referral>0){

        

        foreach($sql_referrals_bonus as $sub_referral){

           $total_bonus += $sub_referral["amount_deposited"];

        }

        

         $total_bonus = "$".(0.05 * $total_bonus);    

       

    }else{

             $total_bonus = "$"."0".".00";

    }   

 

 

 

 

 

 

[Barand]

Your first priority is to get your query right. There are four matching transaction rows with a total of 1,690 (not 1,240) which I have highlighted below. Your total bonus, therefore, should be 84.50.

Your use of GROUP BY without any aggregation gives only one row for each userid. However there are 2 for for user #5 so one is ignored.

SELECT t.userid
     , r.referalid
     , SUM(t.amount_deposited) as deposited
     , SUM(t.amount_deposited) * 0.05 as bonus
FROM transaction_tbl t
     JOIN
     referal_tbl r USING (userid)
GROUP BY userid;

+--------+-----------+-----------+-------+
| userid | referalid | deposited | bonus |
+--------+-----------+-----------+-------+
|      5 |         1 |       750 | 37.50 |
|     18 |         1 |       340 | 17.00 |
|     24 |         1 |       600 | 30.00 |
+--------+-----------+-----------+-------+
                           (1690   84.50)