How to combine two queries with sum

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to combine two queries with sum

Author

Topic

Zifter
Yak Posting Veteran

51 Posts

Posted - 2014-03-25 : 11:31:11

I'm sorry for the bad Subject, but didn't know how to put it.

I made a small example of the problem I'm facing to explain. I'm sure this is a common problem, but my SQL knowledge is to limited to come up with a solution.
Please consider the following example

declare @TableA table(ID int, OrderNr varchar(100))
declare @TableB table(ID int, FKA int, Quantity decimal, Price decimal)
declare @TableC table(ID int, OrderNumber varchar(100), Quantity decimal, Price decimal)

insert into @TableA
values
(1,'1234')

insert into @TableB
values
(1,1,2,2),
(2,1,3,3),
(3,1,4,4)

insert into @TableC
values
(1,'1234',5,5),
(2,'1234',6,6)

-------------------------------------------

select SUM(B.Quantity * B.Price)
from @TableA as A
inner join @TableB as B
  on B.FKA = A.ID
where A.ID = 1

select SUM(C.Quantity * C.Price)
from @TableA as A
inner join @TableC as C  
  on C.OrderNumber = A.OrderNr
where A.ID = 1

-------------------------------------------

select SUM(B.Quantity * B.Price), SUM(C.Quantity * C.Price)
from @TableA as A
inner join @TableB as B
  on B.FKA = A.ID
inner join @TableC as C  
  on C.OrderNumber = A.OrderNr 
where A.ID = 1

The first two queries give the correct result, but when I try to combine them into one query, the sums get multiplied with the number of rows of the other join.
I have no idea how to prevent this. Can somebody please shed some light?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-25 : 11:43:07

Combine the two queries using something like shown below. The join conditions on the two queries are completely different, so joining the way you have done can result in duplicates being added up.

;WITH c1 AS
(
select SUM(B.Quantity * B.Price) MV1
from @TableA as A
inner join @TableB as B
  on B.FKA = A.ID
where A.ID = 1
),
c2 AS 
(
select SUM(C.Quantity * C.Price) MV2
from @TableA as A
inner join @TableC as C  
  on C.OrderNumber = A.OrderNr
where A.ID = 1
)
SELECT * FROM  c1 CROSS JOIN c2;

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-03-25 : 17:06:55

Try this...

SELECT (SELECT SUM(b.price*b.Quantity) FROM @Tableb b WHERE b.FKA = a.ID) AS Sum_1
, (SELECT SUM(c.price*c.Quantity) FROM @Tablec c WHERE c.OrderNumber = a.OrderNr) AS Sum_2
FROM @TableA a

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

Zifter
Yak Posting Veteran

51 Posts

Posted - 2014-03-26 : 04:39:48

Thanks guys for the help.
I'll be using the solution of sqlsaga, looks easier.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources