CODEWITHSUNDEEP
sqlsql-serverfull-outer-join
Test
Test

Reputation: 13

Combining only few columns to 1 in SQL

I have 2 tables tbl1, tbl2.

Sample data of tbl1:

Id Name CreatedOn SpentAmt
1 abc 2023/03/31 1000
2 Test 2023/03/31 14000
3 Mark 2023/03/31 2000
4,5 Robert,Gustin 2023/03/31 700

Sample data of tbl2:

Id Name CreatedOn UsedAmt
1 abc 2023/03/31 2000
7 Grace 2023/03/31 4000
2 Test 2023/03/31 10000
9,1 Mary,abc 2023/03/31 1000

Expected output:

Id Name SpentAmt SpentAmt
1 abc 1000 2000
2 Test 14000 10000
3 mark 2000 null
4,5 Robert,gustin 700 null
7 grace null 4000
9,1 Mary,abc null 1000

This is what I have so far:

SELECT id, name, spentamt AS amt
FROM tbl1
WHERE createdon >= '2021-04-01'
GROUP BY id, name

SELECT id, name, usedamt AS amt
FROM tbl2
WHERE createdon >= '2021-04-01'
GROUP BY id, name

Both individual queries return the expected results, but combining the two isn't working properly

Upvotes: -3

Views: 87

Answers (3)

jarlh
jarlh

Reputation: 44766

You can also UNION ALL the tables and then GROUP BY the result:

select id, Name, sum(SpentAmt), sum(UsedAmt)
from
(
  select id, Name, SpentAmt, null as UsedAmt
  from tbl1 WHERE createdon >= '2023/04/01'
  union all
  select id, Name, null, UsedAmt
  from tbl2 WHERE createdon >= '2023/04/01'
) dt
group by id, Name;

Upvotes: 0

abolfazl sadeghi
abolfazl sadeghi

Reputation: 2368

You can use Cte or Full join to solve the problem


;with _Listtbl1 as (
                    select id,
                           name,
                           sum( spentamt) as amt
                    FROM tbl1
                    where createdon >= '2021-04-01'
                    group by id,
                           name
),_Listtbl2 as (
            select id,
                   name,
                  sum( usedamt)   as usedamt
            FROM tbl2
            where createdon >= '2021-04-01'
            group by id,
                   name
       )

select ISNULL(a.Id,b.Id) as Id
, ISNULL(a.name,b.name) as name
, a.amt as amt
, b.usedamt as usedamt

from _Listtbl1  a
full join _Listtbl2 b on a.Name=b.Name

order by ISNULL(a.Id,b.Id)

Result

Id Id amt usedamt
1 abc 1000 2000
2 Test 14000 10000
3 Mark 2000 NULL
4,5 Robert,Gustin 700 NULL
7 Grace NULL 4000
9,1 Mary,abc NULL 1000

--

You can create insert base data with the following statements:

drop table if exists tbl1
create table tbl1(Id nvarchar(500), Name nvarchar(500), CreatedOn date, SpentAmt bigint)


insert into tbl1(Id ,Name,  CreatedOn,  SpentAmt) values ('1'   ,'abc', '2023/03/31'    ,1000)
insert into tbl1(Id ,Name,  CreatedOn,  SpentAmt) values ('2'   ,'Test',    '2023/03/31'    ,14000)
insert into tbl1(Id ,Name,  CreatedOn,  SpentAmt) values ('3'   ,'Mark',    '2023/03/31'    ,2000)
insert into tbl1(Id ,Name,  CreatedOn,  SpentAmt) values ('4,5' ,'Robert,Gustin',   '2023/03/31'    ,700)



drop table if exists tbl2
create table tbl2(Id nvarchar(500), Name nvarchar(500), CreatedOn date, UsedAmt bigint)


insert into tbl2(Id ,Name,  CreatedOn,  UsedAmt)  values('1'    ,'abc', '2023/03/31'    ,2000)
insert into tbl2(Id ,Name,  CreatedOn,  UsedAmt)  values('7'    ,'Grace',   '2023/03/31',   4000)
insert into tbl2(Id ,Name,  CreatedOn,  UsedAmt)  values('2'    ,'Test',    '2023/03/31',   10000)
insert into tbl2(Id ,Name,  CreatedOn,  UsedAmt)  values('9,1', 'Mary,abc'  ,'2023/03/31',  1000)

Upvotes: 0

GMB
GMB

Reputation: 222462

This looks like a full join; I don't think that you need aggregation here.

select coalesce(t1.id, t2.id) as id, 
    coalesce(t1.name, t2.name) as name, 
    t1.spentamt, 
    t2.usedamt
from (select * from tbl1 where createdon >= '2021-04-01') t1
full join (select * from tbl2 where createdon >= '2021-04-01') t2 on t1.id = t2.id

Note that I moved the date filtering within subqueries, so it happens before the full join.

Upvotes: 1

Related Questions