CODEWITHSUNDEEP
sqloracle-database
user2653353
user2653353

Reputation: 105

Getting sum from same column but on different conditions

Sample Data 

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) ;

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.

Upvotes: 0

Views: 99

Answers (3)

San
San

Reputation: 4538

Take the sum of otsn and dtsn before self joining:

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>

UPDATE: 

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>

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

Your real table probably has NULL values and missing otsn/dtsn values because of which SUM is giving you improper values. better use FULL OUTER JOIN and handle NULLs accordingly as shown.

SELECT  
    NVL(j.otsn,j1.dtsn),  
    SUM( CASE WHEN j.total_js  IS NULL THEN 0 ELSE j.total_js END ) OTSN_TOTAL,  
    SUM( CASE WHEN j1.total_js IS NULL THEN 0 ELSE j1.total_js END ) DTSN_TOTAL  
FROM  
    to_delete j FULL OUTER JOIN  
    to_delete j1  
ON  
    j.otsn = j1.dtsn  
GROUP BY  
    j.otsn,  
    j1.dtsn;

Upvotes: 0

Bikash Ranjan Bhoi
Bikash Ranjan Bhoi

Reputation: 516

Oracle solution for this would be..

    SELECT a.otsn, SUM (a.total_js) OTSN_TOTAL, SUM (b.total_js) DTSN_TOTAL
    FROM to_delete a, to_delete b
   WHERE a.otsn = b.dtsn(+)
   GROUP BY a.otsn;

Thanks Happy Coding :)

Upvotes: 0

Related Questions