如何获得过滤数据的总和

I am new to php, mysql.

我是php,mysql的新手。

I use four columns, companyname, issuedwt, receiptwt & workloss in my database.

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

I use below query to get total of issuedwt, receiptwt & workloss.

我使用下面的查询来获得已发行的wtwt,receiptwt和workloss。

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

I want to use filter option based on companyname.(i.e. company1, company2, company3)

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

If i filter company1, I need total of only company1. What should i do?

如果我过滤公司1,我只需要公司1。我该怎么办?

4 个解决方案

#1

You can simply filter on the companyname with WHERE clause:

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

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

This will give you IssuedTotal, ReceiptTotal, and Balance for only the company1.

这将仅为company1提供IssuedTotal,ReceiptTotal和Balance。

However, if you want to get a list of companynames and the totals, use a GROUP BY companyname, but you still can filter on the companyname:

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

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

#2

Adding the "group by" option in your query will let mysql know which elements belong together, and it will calculate the sums per group:

在查询中添加“group by”选项将让mysql知道哪些元素属于一起,并且它将计算每组的总和:

"SELECT 
  SUM(issuedwt) AS IssuedTotal, 
  SUM(receiptwt) AS ReceiptTotal, 
  SUM(workloss) AS Balance 
  FROM worksheet 
  group by companyname"

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

You can always limit which companies are shown using the 'where' option, (select from TABLE where COLUMN = VALUE) but I am assuming you want to get the data for each and every company; then just use the group by.

您可以随时使用“where”选项限制显示哪些公司,(从TABLE中选择COLUMN = VALUE)但我假设您想要获取每个公司的数据;然后只使用该组。

#3

Use GROUP BY clause along with your query:

将GROUP BY子句与您的查询一起使用:

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

#4

Try this:

SELECT SUM(issuedwt) AS IssuedTotal, SUM(receiptwt) AS ReceiptTotal, 
       SUM(workloss) AS Balance 
FROM `worksheet`
WHERE companyname = 'company1';

If you want company wise data than use below query:

如果您想要公司明智的数据而不是使用以下查询:

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

#1