Click here to Skip to main content
14,930,722 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
query is
SQL
select SUM (p.Product_quan) +b.stock_on_hand  from Product_order as p
 inner join   b_ofc_stock as b on p.item_code =b.item_code
  where p.check_bit=1 and p.Product_name='T' group by b.stock_on_hand

I have two table which contain many of column.. in above query i done a sum of two column from two different table. now i want o display this sum to the column stock_on_hand from the table b_offc_stock... what should i do]

my first table
HTML
item_code	stock_on_hand	Product_name
P001	4	T
P002	3	P
P003	3	L

and second table is
XML
Product_name    Product_quan    Delivery_date   Uname   P_id    Extend_date st_bit  flag    check_bit   item_code
1   T  1   2012-10-22 00:00:00.000 nitin123    16  2012-10-30  0   0   0   P001
2   P  2   2012-10-22 00:00:00.000 nitin123    17  NULL        0   0   1   P002
3   L  1   2012-10-25 00:00:00.000 nitin123    18  NULL        0   0   0   P003
4   P  3   2012-10-22 00:00:00.000 nupur123    19  NULL        0   0   1   P002
5   L  2   2012-10-26 00:00:00.000 nupur123    20  NULL        0   0   1   P003
6   L  2   2012-10-20 00:00:00.000 nitin123    21  2012-10-30  0   0   0   P003
7   T  3   2012-10-23 00:00:00.000 nupur123    22  NULL        0   0   1   P001

i want the table o/p is
HTML
item_code	stock_on_hand	Product_name
P001	7	T
P002	11	P
P003	6	L

Thanks in advance
Posted
Updated 8-Oct-12 6:20am
v5
Comments
VIPR@T 8-Oct-12 6:37am
   
Hi,
Can you please explain with example? What`s your table? what`s your desired output?
Aarti Meswania 8-Oct-12 7:27am
   
give some example data
like product table have these columns & data
second table have..
and desired output should be like this..
it will be solved in short time if you will provide proper example.
Rashid Choudhary 8-Oct-12 8:07am
   
my first table
item_code stock_on_hand Product_name
P001 4 T
P002 3 P
P003 3 L

and second table is

Collapse | Copy Code
Product_name Product_quan Delivery_date Uname P_id Extend_date st_bit flag check_bit item_code
1 T 1 2012-10-22 00:00:00.000 nitin123 16 2012-10-30 0 0 0 P001
2 P 2 2012-10-22 00:00:00.000 nitin123 17 NULL 0 0 1 P002
3 L 1 2012-10-25 00:00:00.000 nitin123 18 NULL 0 0 0 P003
4 P 3 2012-10-22 00:00:00.000 nupur123 19 NULL 0 0 1 P002
5 L 2 2012-10-26 00:00:00.000 nupur123 20 NULL 0 0 1 P003
6 L 2 2012-10-20 00:00:00.000 nitin123 21 2012-10-30 0 0 0 P003
7 T 3 2012-10-23 00:00:00.000 nupur123 22 NULL 0 0 1 P001

i want the table o/p is

item_code stock_on_hand Product_name
P001 7 T
P002 11 P
P003 6 L
Aarti Meswania 8-Oct-12 8:25am
   
input calculations are not match with output

The following expression isn't valid:
SQL
SUM (p.Product_quan) +b.stock_on_hand

The sum is executed for all records so you can't put the addition where you did. You need to separate them, something like this:
SQL
select a.sum_of_quan + b.stock_on_hand from
  (select sum(quan) as sum_of_quan from Products) as a,
  (select stock_on_hand from Stock) as b;


Good luck!
   
Hi rashid,

Ur query is wrong.. Please give more clarification aboout what you exactly need..
it will be helpfull if u post some values of 2 tables.. and what result you need from the tables..

Because if you give SUM() function it will add all the fields of your column, so that then you cant expect it to give a required output. query structure is wrong.. so please give more information about your error.
   
try this

SQL
create table items
(itemcode varchar(5),
stock_hand int,
product_Name varchar(2))


create table prod
(product_name varchar(2),
Productquan int,
checkbit bit,
itemcode varchar(5))

insert into items values ('P001',4,'T')
insert into items values ('P002',3,'P')
insert into items values ('P003',3,'L')

insert into prod values('T',1,0,'P001')
insert into prod values('P',2,1,'P002')
insert into prod values('L',1,0,'P003')
insert into prod values('P',3,1,'P002')
insert into prod values('L',2,1,'P003')
insert into prod values('L',2,0,'P003')
insert into prod values('T',3,1,'P001')

select p.itemcode,sum(stock_hand)+sum(productquan) from items i
inner join prod p on p.itemcode=i.itemcode 
where checkbit=1
group by p.itemcode



the output will be
itemcode (No column name)
P001 7
P002 11
P003 5
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900