I have a problem regarding an SQL query

Question

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 8-Oct-12 0:34am

Rashid Choudhary

Updated 8-Oct-12 6:20am

Kenneth Haugland

v5

Add a Solution

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

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

E.F. Nijboer · Answer 1 · 2012-10-08T01:06:00

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!

Harris débogueur · Answer 2 · 2012-10-08T01:17:00

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.

Santhosh Kumar Jayaraman · Answer 3 · 2012-10-08T03:17:00

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

I have a problem regarding an SQL query

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0