获取变量值到mysql查询的问题 [英] Problem to get value of variable to mysql query

问题描述

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>";
          }
         ?>

我在获取变量 $kpiCode 的值时遇到问题，如果我像 WHERE kpi_code = 'KPI1' 那样手动输入它，它才有效.我想获取每个 KPI 代码的数据总和.已经尝试过 GROUP BY 但所有 total_data 将显示在 1 行中.

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.

推荐答案

要获取每行单个 KPI 代码的总和，您可以使用如下联接:

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

工作数据库小提琴:https://www.db-fiddle.com/f/rSe1MVSsJ7uz63XvJsQUkJ/0

这篇关于获取变量值到mysql查询的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！