add quantity values from two tables

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 2005 Forums

Transact-SQL (2005)

add quantity values from two tables

Author

Topic

c.vaibhav
Starting Member

26 Posts

Posted - 2009-07-30 : 06:21:18

Hi friends,

I have two tables with unique product name column and different quantity..

For eg:

1st table has following values

Product1 - "25"
Product2 - "30"
Product3 - "50"

and my second table has following values

Product2 - "20"
Product3 - "23"

Now I want a resulting select query that will give me the following result

Product1 (=25 + 0) - "25"
Product2 (=30 + 20) - "50"
Product3 (=50 + 23) - "73"

I hope I have explained my query..

Please help..

Regards,
Vaibhav

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-30 : 06:25:42

select t1.price + isnull(t2.price,0) as totalprice from table1 t1
left outer join table2 t2 on t1.product=t2.product

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

c.vaibhav
Starting Member

26 Posts

Posted - 2009-07-30 : 06:27:06

extra information
the two tables that I am talking about in the above post are derived from a select query and that they are not physically present..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-30 : 06:27:24

select ISNULL(a.val ,0) + ISNULL(b.val,0) AS 'Sum'
from tablenamea a
left join tablenameb b on a.productname = b.productname

c.vaibhav
Starting Member

26 Posts

Posted - 2009-07-30 : 06:30:15

Here are those two queries through which I have derived the above two tables..

Query 1:
SELECT Master.ProductDatabase.MaterialID, Master.ProductDatabase.ProductName, Stock.StockDatabase.Stock
FROM Master.ProductDatabase CROSS JOIN
Stock.StockDatabase
Where Master.ProductDatabase.MaterialID like '%PR%'

Query 2:
SELECT Sales.DispatchedProducts.ProductName, sum(Sales.DispatchedProducts.Quantity)
FROM Sales.DispatchDatabase INNER JOIN
Sales.DispatchedProducts ON Sales.DispatchDatabase.DispatchID = Sales.DispatchedProducts.DispatchID
Where Sales.DispatchDatabase.InvoiceDate > '2009-06-06 00:00:00.000'
Group By Sales.DispatchedProducts.ProductName

Thanks..

Vaibhav

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-30 : 06:33:55

select ISNULL(a.val ,0) + ISNULL(b.val,0) AS 'Sum'
from tablenamea a
left join tablenameb b on a.productname = b.productname

use ur query1 in place of the tablenamea as subquery and query2 in place of tablenameb
and try it once.

c.vaibhav
Starting Member

26 Posts

Posted - 2009-07-30 : 06:34:37

thank you very much..

one quick question..

how do I assign tablename to a select query?

thanks again..
Vaibhav

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-30 : 06:36:48

u can get the columns in a temporary table like this
select id,name into #temp from tablename

and then u can use it in that session

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-30 : 06:36:56

quote:
Originally posted by c.vaibhav

thank you very much..

one quick question..

how do I assign tablename to a select query?

thanks again..
Vaibhav

Do you mean this?

Select * from <table_name>

If yes it could be through Dynamic SQL

exec('select * from '+@table_name)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

c.vaibhav
Starting Member

26 Posts

Posted - 2009-07-30 : 06:39:01

you mean to say like the following:

Select select ISNULL(a.Master.ProductDatabase.Quantity ,0) + ISNULL(b.Sales.DispatchedProducts.Quantity,0) AS 'Sum' from (SELECT Master.ProductDatabase.ProductName, Stock.StockDatabase.Stock
FROM Master.ProductDatabase CROSS JOIN
Stock.StockDatabase
Where Master.ProductDatabase.MaterialID like '%PR%') a left join (SELECT Sales.DispatchedProducts.ProductName, sum(Sales.DispatchedProducts.Quantity)
FROM Sales.DispatchDatabase INNER JOIN
Sales.DispatchedProducts ON Sales.DispatchDatabase.DispatchID = Sales.DispatchedProducts.DispatchID
Where Sales.DispatchDatabase.InvoiceDate > '2009-06-06 00:00:00.000'
Group By Sales.DispatchedProducts.ProductName) b on a.Master.ProductDatabase.ProductName = b.Sales.DispatchedProducts.ProductName

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-30 : 06:40:47

quote:
Originally posted by c.vaibhav

you mean to say like the following:

Select select ISNULL(a.Master.ProductDatabase.Quantity ,0) + ISNULL(b.Sales.DispatchedProducts.Quantity,0) AS 'Sum' from (SELECT Master.ProductDatabase.ProductName, Stock.StockDatabase.Stock
FROM Master.ProductDatabase CROSS JOIN
Stock.StockDatabase
Where Master.ProductDatabase.MaterialID like '%PR%') a left join (SELECT Sales.DispatchedProducts.ProductName, sum(Sales.DispatchedProducts.Quantity)
FROM Sales.DispatchDatabase INNER JOIN
Sales.DispatchedProducts ON Sales.DispatchDatabase.DispatchID = Sales.DispatchedProducts.DispatchID
Where Sales.DispatchDatabase.InvoiceDate > '2009-06-06 00:00:00.000'
Group By Sales.DispatchedProducts.ProductName) b on a.Master.ProductDatabase.ProductName = b.Sales.DispatchedProducts.ProductName

Ya it will work!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-07-30 : 06:50:37

; with cte as
(
SELECT Master.ProductDatabase.MaterialID, Master.ProductDatabase.ProductName, Stock.StockDatabase.Stock
FROM Master.ProductDatabase CROSS JOIN
Stock.StockDatabase
Where Master.ProductDatabase.MaterialID like '%PR%'
),
cte1 as
(
SELECT Sales.DispatchedProducts.ProductName, sum(Sales.DispatchedProducts.Quantity) as Quantity
FROM Sales.DispatchDatabase INNER JOIN
Sales.DispatchedProducts ON Sales.DispatchDatabase.DispatchID = Sales.DispatchedProducts.DispatchID
Where Sales.DispatchDatabase.InvoiceDate > '2009-06-06 00:00:00.000'
Group By Sales.DispatchedProducts.ProductName
)
select c.ProductName,coalesce(c1.Quantity,0) + coalesce(c.Stock,0) as Quantity from cte c
full outer join cte c1 on c.ProductName = c1.ProductName

c.vaibhav
Starting Member

26 Posts

Posted - 2009-07-30 : 06:52:19

Thanks Senthil..

It worked..

thank you so much!!!

Vaibhav

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-07-30 : 06:53:31

Ya welcome

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources