mysql - 如何获得过滤数据的总和

Question

我是 php、mysql 的新手。

我在我的数据库中使用了四列，companyname、issuedwt、receiptwt 和 workloss。

我使用下面的查询来获取 issuedwt、receiptwt 和 workloss 的总数。

$result = mysql_query("SELECT SUM(issuedwt) AS IssuedTotal, SUM(receiptwt) AS ReceiptTotal, SUM(workloss) AS Balance FROM `worksheet`")
or die(mysql_error());

我想使用基于 companyname 的过滤器选项。(即 company1、company2、company3)

如果我过滤 company1，我只需要 company1 的总数。我该怎么办？

Answer 1

您可以使用 WHERE 子句简单地过滤 companyname:

SELECT  
  SUM(issuedwt) AS IssuedTotal, 
  SUM(receiptwt) AS ReceiptTotal,
  SUM(workloss) AS Balance 
FROM `worksheet`
WHERE compamnyname = 'company1' -- for example

这将为您提供company1 的IssuedTotal、ReceiptTotal 和Balance。

---

但是，如果您想要获取 companyname 的列表和总计，请使用 GROUP BY companyname，但您仍然可以按公司名称进行过滤:

SELECT companyname, 
  SUM(issuedwt) AS IssuedTotal, 
  SUM(receiptwt) AS ReceiptTotal, 
  SUM(workloss) AS Balance 
FROM `worksheet`
GROUP BY companyname