SQL Sum of items in two tables not equal.

Rog D
Rog D used Ask the Experts™
on
Table A
Item       QOH
123         50
123         45
456           7
456         10
789          30
789          40

Table B
Item       QOH
123         50
123          45
456          4
789          28
789          40


Result Set
Item    QOH  QOH
456     17         4
789     70        68

My goal is to find the items where the Sums are NOT equal.

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Kyle AbrahamsSenior .Net Developer

Commented: 
select a.item, sum(a.qoh) A_QOH, sum(b.qoh) B_QOH
from tbl_a a
join tbl_b b on a.item = b.item
having sum(a.qoh) != sum(b.qoh)

Open in new window

Rog DSQL Developer / Web Development / Business Analysis

Author

Commented:
This does not work as the sums are not calculated properly.

I believe it will require some sort of sub selects?
Software Team Lead
Commented:
try:

declare @TableA table
(
	Item varchar(10),
	QOH int
);
insert into @TableA
values
('123', 50),
('123', 45),
('456', 7),
('456', 10),
('789', 30),
('789', 40);

declare @TableB table
(
	Item varchar(10),
	QOH int
);
insert into @TableB
values
('123', 50),
('123', 45),
('456', 4),
('789', 28),
('789', 40);

with cte_a
as
(
	select Item, sum(QOH) QOH
	from @TableA
	group by Item
), cte_b as
(
	select Item, sum(QOH) QOH
	from @TableB
	group by Item
)
select a.Item, a.QOH, b.QOH
from cte_a a
inner join cte_b b on a.Item = b.Item
where a.QOH <> b.QOH

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Did you try an UNION?
SELECT item, SUM(QOH)
FROM tableA
GROUP BY item
UNION
SELECT item, SUM(QOH)
FROM TableB
GROUP BY item

Open in new window

Rog DSQL Developer / Web Development / Business Analysis

Author

Commented:
Great this solution worked with correct results.

Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial