Forum Discussion
Combine data from two tables
So I have these 2 tables (lets just call them tableA and tableB):
I would like to merge them on date, site and devicecategory, so I get this result:
What would be the best way to go about this? Currently I do it by union and group by/sum:
select date, siteid, DeviceCategory, sum(a) as a, sum(b) as b, sum(c) as c, sum(d) as d, sum(e) as e, sum(f) as f, sum(g) as g
from
(
select date, siteid, DeviceCategory, a, b, c, d, e, 0 as f, 0 as g,
from TableA
where date = '2023-01-09' and SiteId=4
union
select date, siteid, DeviceCategory, 0 as a, 0 as b, 0 as c, 0 as d, 0 as e, f, g
from TableB
where mt.date = '2023-01-09' and SiteId=4
) as t
group by date, siteid, DeviceCategory
But I feel like there have to be a better way.
3 Replies
gulgil , for the wanted result you have to use JOIN instead of UNION, see
Joins (SQL Server) - SQL Server | Microsoft Learn
For a more detailed answer, please post table design as DDL, some sample data as DML statement and the expected result.
- If I use join then I lose data because I join on both date and name. But some data only exists in left or right. I expect to use an outer join then, but I can't get it correct.
I have this for test
CREATE TABLE [dbo].[TableA](
[date] [date] NOT NULL,
[name] [nchar](10) NOT NULL,
[a] [int] NOT NULL,
CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED
(
[date] ASC,
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[TableB](
[date] [date] NOT NULL,
[name] [nchar](10) NOT NULL,
[b] [int] NOT NULL,
CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED
(
[date] ASC,
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
insert into TableA(date, name, a) values(GETDATE()-2, 'lorem', 1), (GETDATE()-1, 'ipsum', 1),(GETDATE()-1, 'dolor', 1);
insert into TableB(date, name, b) values(GETDATE()-1, 'dolor', 2), (GETDATE(), 'sit', 3);
select date, name, sum(a) as a, sum(b) as b
from
(
select date, name, a, 0 as b
from TableA
union
select date, name, 0 as a, b
from TableB
) as t
group by date, name
It gives this result.
date name a b
2023-09-25 lorem 1 0
2023-09-26 dolor 1 2
2023-09-26 ipsum 1 0
2023-09-27 sit 0 3
Note that row 2023-09-25 'lorem' only exists in TableA, and therefor is "0" in column b.
Note that row 2023-09-25 'dolor' exists in both, and therefor have data in both column a and b.
Note that row 2023-09-27 'sit' only exists in TableB, and therefor is "0" in column a.But some data only exists in left or right.
gulgil , and for this scenario we have (FULL) OUTER JOINS, so use them
