Combining only few columns to 1 in SQL

Question

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

Answer 1

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.

Answer 2

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)

Answer 3

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;