Lets say we are executing this query below to retrieve each customer and the amount associated to a table 

"INSERT INTO tblReceiptDue (Dealer, Amount) SELECT CustomerName, SUM(CASE WHEN VoucherType = 'Sales' then Outbound ELSE  - Inbound END) AS AMOUNT from tblSaleStatementCustomer  WHERE CustomerType = 'Dealer' GROUP BY CustomerName"

Which display the data like below

DEALER                AMOUNT
------------------------------------------------
ABC                          2000
XYZ                          1000    

However I have one more table TABLE2 which contains two columns

DEALER                OPENING
-------------------------------------------------------
ABC                               5000
XYZ                               7000

I want to modify my query in such a way that AMOUNT column should also add OPENING from TABLE2 So that I must get the result like below

DEALER                AMOUNT
------------------------------------------------
ABC                            7000
XYZ                            8000    

Can somebody help ?

Thanks

You can JOIN the tables  on Dealer for example  like

SELECT Dealer, AMOUNT+OPENING as AMOUNT FROM t1 JOIN t2 ON .......

Thanks for your reply, Uri Dimant !

Actually I am new to t-sql, could you please try and show me the query by modifying my query

"INSERT INTO tblReceiptDue (Dealer, Amount) SELECT CustomerName, SUM(CASE WHEN VoucherType = 'Sales' then Outbound ELSE  - Inbound END) AS AMOUNT fromtblSaleStatementCustomer  WHERE CustomerType = 'Dealer' GROUP BY CustomerName"

Thanks

Hi Sid,

Please try below script to achieve your scenario,

Declare @tempt1 table
(Dealer VARCHAR(50),AMOUNT INT)
insert into @tempt1
select 'ABC',2000 Union ALL
select 'XYZ',1000 

Declare @tempt2 table
(Dealer VARCHAR(50),OPENING INT)
insert into @tempt2
select 'ABC',5000 Union ALL
select 'XYZ',7000 

Select * from @tempt1
Select * from @tempt2

Select A.Dealer, SUM(AMOUNT+OPENING) as AMOUNT from @tempt1 A
Join @tempt2 B on A.DEALER=B.DEALER
Group by A.Dealer

Hi Sid,

If you are new in SQL then I suggest you to please learn and understand the SQL scripting from below link.

http://www.w3schools.com/sql/

Sid,

Why you have written CASE inside SUM.

CASE WHEN VoucherType = 'Sales' then Outbound ELSE  - Inbound END

Could you please clear here what you want to achieve then probably we can give you proper answer.

Thanks Kamlesh Kumar,

Lets say we are executing this query below to retrieve each customer and the amount associated to a table 

"INSERT INTO tblReceiptDue (Dealer, Amount) SELECT CustomerName, SUM(Inbound) AS AMOUNT fromtblSaleStatementCustomer GROUP BY CustomerName"

Which display the data like below

DEALER                AMOUNT
------------------------------------------------
ABC                          2000
XYZ                          1000    

However I have one more table TABLE2 which contains two columns

DEALER                OPENING
-------------------------------------------------------
ABC                               5000
XYZ                               7000

I want to modify my query in such a way that AMOUNT column should also add OPENING from TABLE2 So that I must get the result like below

DEALER                AMOUNT
------------------------------------------------
ABC                            7000
XYZ                            8000    

Can you help me now, I hope I cleared everything.

Thanks

Please modify your as below script,

INSERT INTO tblReceiptDue (Dealer, Amount)
Select A.Dealer [CustomerName], SUM(AMOUNT+OPENING) as AMOUNT from tblSaleStatementCustomer A
Join TABLE2 B on A.DEALER=B.DEALER

you can achieve your result with using JOIN, Please try this. 

And Please read my first answer also, already gave you simple and understandable script.

Hi Sid,

Kamlesh should work in your case

Declare @tempt1 table
(Dealer VARCHAR(50),AMOUNT INT)
insert into @tempt1
select 'ABC',2000 Union ALL
select 'XYZ',1000 

Declare @tempt2 table
(Dealer VARCHAR(50),OPENING INT)
insert into @tempt2
select 'ABC',5000 Union ALL
select 'XYZ',7000 

Select * from @tempt1
Select * from @tempt2

Select A.Dealer, SUM(AMOUNT+OPENING) as AMOUNT from @tempt1 A
Join @tempt2 B on A.DEALER=B.DEALER
Group by A.Dealer

Here you go

WITH cte
AS
(
SELECT CustomerName, SUM(CASE WHEN VoucherType = 'Sales' then Outbound ELSE  - Inbound END) AS AMOUNT 
from 
tblSaleStatementCustomer  WHERE CustomerType = 'Dealer' GROUP BY CustomerName
) INSERT INTO tblReceiptDue (Dealer, Amount) 
  SELECT CustomerName ,SUM(AMOUNT+OPENING) as AMOUNT FROM cte JOIN table1 ON
   cte.CustomerName=table1.dealre
   GROUP BY CustomerName