mysql根据不同的列值与group by进行求和

本文介绍了mysql根据不同的列值与group by进行求和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 create table t ( `place_id` int(11) NOT NULL , `area_sq` int (11) NOT NULL, `nxx` int(11) NOT NULL ); insert into t values(1, 50, 1); insert into t values(2, 90, 2); insert into t values(2, 20, 1); insert into t values(2, 10, 0); insert into t values(2, 10, 1); insert into t values(3, 10, 3); | PLACE_ID | AREA_SQ | NXX | |----------|---------|-----| | 1 | 50 | 1 | | 2 | 90 | 2 | | 2 | 20 | 1 | | 2 | 10 | 0 | | 2 | 10 | 1 | | 3 | 10 | 3 |

以上是我的名为 t 的表格。我需要得到以下结果

above is my table called t. I need to get the following result

| PLACE_ID | SUM(AREA_SQ) |nxx | |----------|--------------|----| | 1 | 50 | 1| | 2 | 100 | 2| | 2 | 40 | 1| | 3 | 10 | 3|

我想要nxx = 0的area_sq的总和加上area_sq的总和

I want the sum of area_sq of same nxx plus sum of area_sq if nxx=0

我目前的查询是这样的。但是，如果nxx = 0作为单独的行，我不希望总和area_sq，而如果nxx = 0，我想将sum（area_sq）添加到其他行。所以想要按nxx进行分组，但对于nxx = 0，应将area_sq添加到其他nxx值。我真的很抱歉不正确的英语。我尝试了不同的东西，但都没有成功。

my current query is like this. But I do not want sum of area_sq if nxx=0 as separate row, instead I want to add sum(area_sq) to others if nxx=0. So want to group by nxx as well but for nxx=0 area_sq should be added to the other nxx values. I am really sorry for incorrect English. I tried different things but none was successful. Thanks in advance.

select place_id,sum(area_sq) from t group by place_id,nxx

推荐答案

您可以有一个额外的子查询作为连接，它分别计算<$ c $ b

You can have an extra subquery to be join which separately calculates the total sum of nxx = 0.

SELECT t.PLACE_ID, SUM(t.AREA_SQ) + COALESCE(s.AREA_SQ,0) totalSum, NXX FROM t LEFT JOIN ( SELECT PLACE_ID, SUM(AREA_SQ) AREA_SQ FROM t WHERE NXX = 0 GROUP BY PLACE_ID ) s ON t.PLACE_ID = s.PLACE_ID WHERE NXX <> 0 GROUP BY t.PLACE_ID, NXX ORDER BY t.PLACE_ID, totalSum DESC

• SQLFiddle演示
• SQLFiddle Demo