求和多列的 PHP 和 MYSQL 查询

Question

我想显示来自数据库的数据，计算每一行的总和和平均值，它看起来像这样:

但是，目前，我只能显示从 year 到 dec 的列，并且我已经完成了这样的基本查询:

降雨.php

    <?php
$servername = "xxxxx";
$username = "xxxx";
$password = "xxxx";
$dbname = "xxxxx";

$conn = new mysqli($servername, $username, $password, $dbname);


if ($conn->connect_error) {
 die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM rainfall";
$result = $conn->query($sql);

if ($result->num_rows >0) {
 // output data of each row
 while($row[] = $result->fetch_assoc()) {

 $tem = $row;

 $json = json_encode($tem);

 }
} else {
 echo "0 results";
}
 echo $json;
$conn->close();
?>

我的降雨表结构:

CREATE TABLE IF NOT EXISTS `rainfall` (
  `rf_id` int(11) NOT NULL AUTO_INCREMENT,
  `estate_id` int(11) NOT NULL,
  `year_rainfall` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
  `jan` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `feb` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `mar` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `apr` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `may` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `jun` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `jul` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `aug` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `sep` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `oct` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `nov` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `dec` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`rf_id`)
) ;

我应该在 php 代码中添加什么来显示如上图所示的计算(总和和平均值)？需要你的建议...

Answer 1

用这个替换你的 SQL 查询:

SELECT `Year`,
    jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, `dec`,
    jan + feb + mar + apr + may + jun + jul + aug + sep + oct + nov + `dec` AS total
FROM rainfall

UNION ALL

SELECT 'Mean',
    avg(jan), avg(feb), avg(mar), avg(apr), avg(may), avg(jun),
    avg(jul), avg(aug), avg(sep), avg(oct), avg(nov), avg(`dec`),

    avg(jan) + avg(feb) + avg(mar) + avg(apr) + avg(may) + avg(jun) +
    avg(jul) + avg(aug) + avg(sep) + avg(oct) + avg(nov) + avg(`dec`)
FROM rainfall