query for sum of two fields

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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)

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	create table Table1 (IssNo varchar(10), Qty int) insert Table1 values('120', 10 ) create table Table2 (IssNo varchar(10), Qty int) insert Table2 values('120', 20 ), ('124', 30 ) --Query SELECT table1.issno, sum(table2.qty) sumQty FROM table1 INNER JOIN table2 on Table2.IssNo like substring(Table1.IssNo, 1, 2)+ '%' GROUP BY table1.issno --OUPUT issno sumQty 120 50 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

create table Table1 (IssNo varchar(10), Qty  int)
insert Table1 values('120', 10 )

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


--Query
SELECT table1.issno, sum(table2.qty) sumQty 
FROM table1 
INNER JOIN table2 on Table2.IssNo like substring(Table1.IssNo, 1, 2)+ '%'
GROUP BY table1.issno

 --OUPUT
issno	sumQty
120		50

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

0	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

0	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

Latest Posts