what is the query for sum of 2 fields

Posted by Oswaldlily under Sql Server on 2/21/2014 | Points: 10 | Views : 1949 | Status : [Member] | Replies : 3
Table1

IssNo Qty
120 10



Table2

IssNo Qty
120 20
124 30

use this also :
From Table1-> substring(Table1.IssNo,1,2),at end I need to add "%" (eg) 12%

Now I need to check this like condition in table2 For Qty..And i need to add all Qty of 12% (eg) 20 and 30 will come..after SUM=> 50

My query is Select Name,id,IssNo,address,Qty from Table1(here Qty alone should be sum of Table2)

Expected o/p

IssNo Qty
120 50(Sum of Qty from Table2)




Responses

Posted by: Bandi on: 2/21/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
select table1.issno, sum(table2.qty) sumQty
from table1 join table2 on SUBSTRING(Table1.IssNo,1,2)=substring(Table2.IssNo,1,2)
group by table1.issno

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 2/28/2014 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Click on "Mark This Response As Answer " link if the above query helped you

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Saranpselvam on: 4/4/2014 [Member] Starter | Points: 25

Up
0
Down
create table t1 (IssNo varchar(10), Qty int)
insert t1 values('120', 10 )

create table t2 (IssNo varchar(10), Qty int)
insert t2 values('120', 20 ), ('124', 30 )


-- Query
SELECT t1.IssNo, sum(t2.Qty) sumQty
FROM t1
INNER JOIN t2 on t2.IssNo like substring(t1.IssNo, 1, 2)+ '%'
GROUP BY t1.IssNo


Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response