如何在 PHP 中显示 MySQL 列的总数 [英] How to display total of MySQL column in PHP

问题描述

我只是遇到了一些麻烦，对 MySQL 的经验不是很丰富.这是我的代码:

I am just having a bit of trouble, not super experienced with MySQL. Here's my code:

    // Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT id, client, package, rate, term_start, term_end, last_billed FROM clients";
$result = $conn->query($sql);


    echo "<table><tr><th>Client</th><th>Package</th><th>Rate</th><th>Term Start</th><th>Term End</th><th>Last Billed</th></tr>";
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>" . $row["client"] . "</td><td>" . $row["package"]. "</td><td>$" . $row["rate"]. "</td><td>" . date("M\(m\) j, Y", strtotime($row["term_start"])) . "</td><td>" . date("M\(m\) j, Y", strtotime($row["term_end"])) . "</td><td>" . date("M\(m\) j, Y g:i a", strtotime($row["last_billed"])) . "</td><td><div class='tags'><a href=''>Details</a><a href=''>Send Invoice</a></div></td></tr>";
    }

    echo "<tr><td></td><td></td><td>" . $row["rate_sum"] . "</td></tr>";
    echo "</table>";

$conn->close();

如您所见，我希望将列行中所有内容的总和相加.我知道它是 SELECT SUM(rate) AS rate_sum FROM clients 让它以rate_sum"返回，但是我如何在进行 SQL 调用的同时进行另一个调用?

As you can see I want the sum of all things in column row to total out. I know it's SELECT SUM(rate) AS rate_sum FROM clients to get it to return at "rate_sum" but how do I make the SQL call while making the other one at the same time?

推荐答案

这可以像这样在查询中完成.

this can be done in the query like so.

SELECT id, client, package, rate, SUM(rate) as total, term_start, term_end, last_billed 
FROM clients
group by id with Rollup

最后一行的 id 将为 null 和您想要的总数.

the last row will have an id of null and the total you want.

https://dev.mysql.com/doc/refman/5.6/en/group-by-modifiers.html

这不是 ROLLUP 最常见的用法，当您已经在进行聚合时，它更常用作超级聚合，例如按月分组销售并获得总计.但是，如果您需要数据来自查询而不是在外部计算，则对 id 进行分组将使其就像一个简单的总和.它在排序和限制方面确实有缺点.

This is not be the most common use of ROLLUP, its more often used as an super aggrigate when you are already doing an aggrigate, say group sales by month and get the grand total. However if you need the data to come from the query rather than being calculated outside, grouping on id will make it just like a simple sum. It does have downsides regarding sorting and limit.

这篇关于如何在 PHP 中显示 MySQL 列的总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！