从相同的列获得总和，但在不同的条件下(Getting sum from same column but on different conditions)

样本数据

create table to_delete ( otsn number, dtsn number,total_js number) insert into to_delete values (1,5,2) ; insert into to_delete values (2,4,3) ; insert into to_delete values (3,3,5) ; insert into to_delete values (4,2,7) ; insert into to_delete values (5,1,10) ;

我需要根据otsn和dtsn获得total_js总和

SELECT j.otsn, SUM(j.total_js), j1.dtsn, SUM(j1.total_js) FROM to_delete j, to_delete j1 WHERE j.otsn = j1.dtsn GROUP BY j.otsn, j1.dtsn;

但是在实际的表中我没有得到预期的结果，要么得到otsn或dtsn的正确值，而不是两者都得到。

我想要的输出是

TSN, sum(total_js) of otsn, sum(total_js) of dtsn 1, 2 , 10 2, 3

，7

tsn代表中转站，otsn代表原点中转站，dtsn代表目的地中转站。所以有一个单独的表，其中orgin和dest都有总计数。

如果我必须计算有多少人进入otsn然后otsn，计算（total_js）如果我必须计算有多少人从dtsn下来然后dtsn，count（total_js）

因此，在最终输出中，我需要一个带有传入总数和传出总数的TSN。

Sample Data

I need to get total_js sum based on otsn and dtsn

SELECT j.otsn, SUM(j.total_js), j1.dtsn, SUM(j1.total_js) FROM to_delete j, to_delete j1 WHERE j.otsn = j1.dtsn GROUP BY j.otsn, j1.dtsn;

but in the real table I am not getting the intended result, either am getting the right value for otsn or dtsn but not both.

The output I desire is

TSN, sum(total_js) of otsn, sum(total_js) of dtsn 1, 2 , 10 2, 3

, 7

tsn stands for transit stop, otsn stands for origin transit stop and dtsn stands for dest transit stop. so there is a single table where both orgin and dest are there with total counts.

If i have to calculate how many people entered into otsn then otsn, count(total_js) if i have to calculate how many people get off from dtsn then dtsn,count(total_js)

So in the final output I need a TSN with the incoming total and outgoing total.

最满意答案

在自我加入之前取otsn和dtsn的总和：

SQL> create table to_delete ( otsn number, dtsn number,total_js number) ; Table created SQL> insert into to_delete values (1,5,2) ; 1 row inserted SQL> insert into to_delete values (2,4,3) ; 1 row inserted SQL> insert into to_delete values (3,3,5) ; 1 row inserted SQL> insert into to_delete values (4,2,7) ; 1 row inserted SQL> insert into to_delete values (5,1,10) ; 1 row inserted SQL> WITH cte_otsn AS 2 (SELECT otsn, 3 SUM(total_js) o_total 4 FROM to_delete 5 GROUP BY otsn), 6 cte_dtsn AS 7 (SELECT dtsn, 8 SUM(total_js) d_total 9 FROM to_delete 10 GROUP BY dtsn) 11 SELECT d.dtsn, 12 o.o_total, 13 d.d_total 14 FROM cte_otsn o 15 JOIN cte_dtsn d 16 ON d.dtsn = o.otsn; DTSN O_TOTAL D_TOTAL ---------- ---------- ---------- 1 2 10 2 3 7 5 10 2 4 7 3 3 5 5 SQL>

更新：

SQL> create table to_delete (OTSN number,DTSN number,OHR number,DHR number,TOTAL_JS number); Table created SQL> INSERT INTO to_delete VALUES (1,5,2,3,2); 1 row inserted SQL> INSERT INTO to_delete VALUES (2,4,2,4,3); 1 row inserted SQL> INSERT INTO to_delete VALUES (3,3,3,3,5); 1 row inserted SQL> INSERT INTO to_delete VALUES (4,2,1,4,7); 1 row inserted SQL> INSERT INTO to_delete VALUES (5,1,4,1,10); 1 row inserted SQL> WITH cte_data(tsn,hr,total,tsn_type) AS 2 (SELECT otsn, 3 ohr, 4 total_js, 5 'o' 6 FROM to_delete 7 UNION ALL 8 SELECT dtsn, 9 dhr, 10 total_js, 11 'd' 12 FROM to_delete) 13 SELECT tsn, hr, COALESCE(OCNT, 0) AS ocnt, COALESCE(dcnt, 0) AS dcnt 14 FROM cte_data 15 PIVOT (SUM(total) FOR tsn_type IN ('o' AS "OCNT", 'd' AS "DCNT")); TSN HR OCNT DCNT ---------- ---------- ---------- ---------- 4 1 7 0 2 4 0 7 4 4 0 3 1 2 2 0 2 2 3 0 3 3 5 5 1 1 0 10 5 4 10 0 5 3 0 2 9 rows selected SQL>

Take the sum of otsn and dtsn before self joining:

UPDATE:

更多推荐