如何计算pf及如何显示pf总量

Question

这里我想要计算PF，我们每个月都要扣除pf，现在我想显示pf的总amount.this是我的数据库表结构。

id    first_name   pf_amount   pf_month    badge_number

1      Kani         200         01-2017      01

2      Mahesh       250         01-2017      02

3      Kani         200         02-2017      01

4      Mahesh       250         02-2017      02

在我的列表页面中，我想显示badge_number (01)有400，badge_number (02)有500。在这里，badge_number是唯一的

我编写了这样的查询，但是在这里我获得了所有的数据，如何根据我的需求进行处理？

$check = mysql_query("SELECT * FROM pf_history");
while($row = mysql_fetch_array($check)) {
   echo $row['pf_amount'];
}

Answer 1

在查询中使用GROUP和SUM()：

SELECT first_name, pf_month, SUM(pf_amount) as total FROM pf_history GROUP BY badge_number

代码：

$check = mysqli_query("SELECT first_name, pf_month, SUM(pf_amount) as total FROM pf_history GROUP BY badge_number");
while($row = mysqli_fetch_array($check)) {
  echo $row['total'];
}

用于group和sum()的引用：

按组

SUM()

Answer 2

您应该使用sum()和group by，如下所示：

SELECT first_name, pf_month, badge_number, SUM(pf_amount) as total_pf_amount
FROM pf_history 
GROUP BY badge_number

PHP：

$check = mysql_query("SELECT first_name, pf_month, badge_number, SUM(pf_amount) as total_pf_amount
    FROM pf_history 
    GROUP BY badge_number");
while($row = mysql_fetch_array($check)) {
    echo $row['total_pf_amount'];
}

参考文献