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 |
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|
I want the sum of area_sq of same nxx plus sum of area_sq if nxx=0
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
解决方案
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