How to Sum amount of same role records?

Question

I want to get amount of same role in one sum. When I fetch a records of agent details introduced by some agent (11150000001). It will return lot of agents records with different role(1000,2000,3000,4000,5000,6000).

From that I need to get the agents business amount total. Now I'm getting it. But If there is some agent in same role I want to get that same role agent's amount in single amount, but I'm getting separately.

  //get all employee under one employee
            $rank1=''; $rank2='';$rank3='';$rank4='';$rank5='';$rank6='';
               $get_employee="SELECT emp_code,intro_code FROM emp_details WHERE intro_code='".$emp_code."'";
               $exe_employee=mysql_query($get_employee);

               while($fetch_emp=mysql_fetch_assoc($exe_employee))
               {
                     $total_amount1=0;
                     $role='';
                   $employee_code=$fetch_emp['emp_code'];
                   //echo $employee_code."<br>";
                   $get_premium="SELECT emp_code,user_role,premium_amount FROM business_details WHERE emp_code='".$employee_code."'";
                  //echo $get_premium."<br>";
                   $exe_premium=mysql_query($get_premium);

                   while($fetch_amount=mysql_fetch_array($exe_premium))
                   {
                      $total_amount1 +=$fetch_amount['premium_amount'];
                      $role=$fetch_amount['user_role'];  
                   }
              }
      echo $role."-".$total_amount1."<br>"; 
       // Role-Amount
       // 5000-75000
        //  5000-105000
        //3000-15000

In the above image I'm getting 10,500 in rank 5, but I want to get the 75000+10500 in rank 5 column.

Below is my table structure:

Answer 1

The entire block of code than be replaced by a single SQL query. You want to create a pivot table.

Lets start by a relatively simple query that fetches the total premium_amount of all the employee-role combinations:

SELECT emp_code,
       user_role,
       SUM(premium_amount) AS total_amount
FROM business_details
GROUP BY emp_code, user_role;

This would yield something like this:

emp_code    | user_role | total_amount
-----------------------------------
15040000001 | 3000      |  15000     
15040000001 | 5000      | 180000
11130000001 | 4000      |  30000
11130000001 | 1000      |   5000

This already contains all the information you want in your table, but not in the correct layout. Instead of using PHP to reformat this into the rows/columns you want for your table, SQL can do it for us.

Instead of grouping by user_role, we're going to specify the different values user_role can take as different columns:

SELECT emp_code,
       SUM(CASE WHEN user_role=1000 THEN premium_amount ELSE 0 END) AS rank1,
       SUM(CASE WHEN user_role=2000 THEN premium_amount ELSE 0 END) AS rank2,
       SUM(CASE WHEN user_role=3000 THEN premium_amount ELSE 0 END) AS rank3,
       SUM(CASE WHEN user_role=4000 THEN premium_amount ELSE 0 END) AS rank4,
       SUM(CASE WHEN user_role=5000 THEN premium_amount ELSE 0 END) AS rank5,
       SUM(CASE WHEN user_role=5000 THEN premium_amount ELSE 0 END) AS rank6
FROM business_details
GROUP BY emp_code;

The result would be:

emp_code    | rank1 | rank2 | rank3 | rank4 | rank5  | rank6
------------------------------------------------------------
15040000001 |     0 |     0 | 15000 |     0 | 180000 |     0
11130000001 |  5000 |     0 |     0 | 30000 |      0 |     0

Bonus advise

The PHP code you've posted contains a glaring security flaw that leaves your database (or probably your entire system) wide open to anyone. SQL injection is not fun to have in your web application! I've written about it in a previous answer.

Answer 2

Try this...

 $get_premium="SELECT emp_code,user_role,premium_amount,SUM(premium_amount) FROM business_details WHERE emp_code='".$employee_code."' group by user_role" ;