Sum from two tables thorugh join one table has two records and other has three against same id

Question

0.00/5 (No votes)

See more:

Dear Sir

i have two tables

Table X

Sheets	PoNo  invno     Itemid                   
2350	126     3         111

2078	129     3         111

Table Y

Sheets	PoNo  invno     itemid
2300    126     3        111

50      126     3        111

2078	129     3        111

when i join both tables by pono,invno and itemid and sum table X sheets it shows double sum 6678 . but the total must be 2350+2078=4428
please give the any solution in query
the query is

select sum(pd.sheets) as tot from X pd,Y q where pd.itemid=q.itemid and pd.pono=q.pono and q.invno=p.invno and q.itemcode=518

Posted 6-Oct-15 0:29am

rajbir singh thind

Updated 6-Oct-15 1:39am

Naveen.Sanagasetti

v3

Add a Solution

Comments

vangapally Naveen Kumar 6-Oct-15 7:16am

Use 'Group by' clause.I think the query is calculating like this 2350+2300+50+2078(it is taking the two records from tableY for invo 3, pono 126 and itemID111).

2 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)

Naveen.Sanagasetti · Answer 1 · 2015-10-06T01:59:00

Refer below sample

SQL

declare @tabX table
(
    Sheets int,
	PoNo int,
	invno int,
	ItemId int
)

declare @tabY table
(
    Sheets int,
	PoNo int,
	invno int,
	ItemId int
)

insert into @tabX
values (2350,126,3, 111),(2078,129,3,111)

insert into @tabY
values(2300,126,3,111),(50,126,3,111),(2078,129,3,111)

select x.Sheets
into #result
from @tabX X,
     @tabY Y
where X.PoNo=y.PoNo
      and x.ItemId=y.ItemId
	  and x.invno=y.invno
Group by x.Sheets

select sum(sheets) from #result

vangapally Naveen Kumar · Answer 2 · 2015-10-06T19:22:00

Solution 2

See this link you may get some idea

http://stackoverflow.com/questions/6466532/sum-of-calculated-field-returns-wrong-result-in-ms-access-query[^]

Posted 6-Oct-15 19:22pm

vangapally Naveen Kumar