CODEWITHSUNDEEP
sqloracle-databaseaggregate-functions
Rohit
Rohit

Reputation: 39

How to sum two different fields from two tables with one field is common

I have two tables Sales and Charges.

Tables having data as:

  'Sales'                                     'Charges'
SID    F_AMT                           SID            C_AMT
1       100                            1               10
1       100                            1               10
1       100                            1               20
1       200                            2               20
2       200                            2               10
2       300                            3               20
4       300                            3               30
4       300                            3               10
4       300                            5               20
4       200                            5               10

I want the output as below:

SID       Total_Fees     Total_charges
1           500              40
2           500              30
3            0               60
4           1100             0
5            0               30

Upvotes: 2

Views: 55

Answers (3)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Use full join and nvl():

select sid, nvl(sum(f_amt), 0) fees, nvl(sum(c_amt), 0) charges
  from sales s 
  full join charges c using (sid)
  group by sid 
  order by sid

Demo:

with sales(sid, f_amt) as (
    select 1, 100 from dual union all select 1, 100 from dual union all 
    select 1, 100 from dual union all select 1, 200 from dual union all 
    select 2, 200 from dual union all select 2, 300 from dual union all 
    select 4, 300 from dual union all select 4, 300 from dual union all 
    select 4, 300 from dual union all select 4, 200 from dual ),
charges (sid, c_amt) as (
    select 1, 10 from dual union all select 1, 10 from dual union all
    select 1, 20 from dual union all select 2, 20 from dual union all
    select 2, 10 from dual union all select 3, 20 from dual union all
    select 3, 30 from dual union all select 3, 10 from dual union all
    select 5, 20 from dual union all select 5, 10 from dual )
select sid, nvl(sum(f_amt), 0) fees, nvl(sum(c_amt), 0) charges
  from sales s 
  full join charges c using (sid)
  group by sid 
  order by sid

Output:

   SID       FEES    CHARGES
------ ---------- ----------
     1       1500        160
     2       1000         60
     3          0         60
     4       1100          0
     5          0         30

Upvotes: 1

APC
APC

Reputation: 146209

Assuming you want to do it for the whole tables this is the simplest approach:

Select Sid
            , Sum(f_amt) as total_fees
            , Sum(c_amt) as total_charges
From ( select sid, f_amt, 0 as c_amt
              From sales
              Union all
              select sid, 0 as f_amt, c_amt
              From charges
          )
Group by sid

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

You could use conditional aggregation:

SELECT SID,
      COALESCE(SUM(CASE WHEN t=1 THEN AMT END),0) AS Total_Fees,
      COALESCE(SUM(CASE WHEN t=2 THEN AMT END),0) AS Total_Charges
FROM (SELECT SID, F_AMT AS AMT, 1 AS t
      FROM Sales
      UNION ALL
      SELECT SID, C_AMT AS AMT, 2 AS t
      FROM Charges) sub
GROUP BY SID
ORDER BY SID;

DB Fiddle Demo

Upvotes: 0

Related Questions