SQL Sum of items in two tables not equal.

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.
LVL 8
Rog DSQL Developer / Web Development / Business AnalysisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented: 
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 AnalysisAuthor Commented:
This does not work as the sums are not calculated properly.

I believe it will require some sort of sub selects?
Ryan ChongSoftware Team LeadCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
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 AnalysisAuthor Commented:
Great this solution worked with correct results.

Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.