php - Problem to get value of variable to mysql query

Question

Table Name: data_detail

kpi_code | result_data
____________________________________
KPI1     | 100
KPI1     | 150
KPI2     | 30
KPI2     | 50

Expected Result in database:
kpi_code | total_data
____________________________________
KPI1     | 250
KPI2     | 80

Expecting Output:
Kpi Code | Data | Total |
____________________________________
KPI1     | 100  | 250   |
KPI1     | 150  | 250   |
KPI2     | 30   | 80    |
KPI2     | 50   | 80    |
This is the output i get:

kpi_code  |  total_data

KPI1      |       250
                   80
____________________

KPI2      |       250
                   80
<?php

          $sqlCount = "SELECT kpi_code, SUM(result_data) AS total_data FROM data_detail WHERE kpi_code='$kpiCode'";
          // $sqlCount = "SELECT kpi_code,assign, SUM(result_data) AS total_data FROM data_detail  WHERE assign='$username'";

          $resultCount = $conn->query($sqlCount);
          while($rowCount = $resultCount->fetch(PDO::FETCH_ASSOC)){
            $kpiCode= $rowCount['kpi_code'];
            echo "<div>".$rowCount['total_data']."</div>";
          }
         ?>

I have problem in getting the value of variable $kpiCode, if I key in manually like WHERE kpi_code = 'KPI1' only it works. I want to get the sum of data for each KPI Code. Already tried GROUP BY but all the total_data will display in 1 row.

Answer 1

To get the entire sum for individual KPI code on each row, you can use a join like below:

select data_detail.kpi_code,data_detail.result_data,derived.total_data 
from (
        SELECT kpi_code, SUM(result_data) AS total_data 
        FROM data_detail
        group by kpi_code 
     ) derived
join data_detail
on derived.kpi_code = data_detail.kpi_code

A working DB Fiddle: https://www.db-fiddle.com/f/rSe1MVSsJ7uz63XvJsQUkJ/0