sql aggregate function with left join

Question

0.00/5 (No votes)

See more:

My sql query is :

SQL

select s.no,s.name,sum(e.qty),sum(x.qty) from Stock s left join entry e on s.no=e.no left join exit x on s.no=x.no group by s.no,s.name

I have three tables Stock,entry,exit. no column in stock is a primary key and no column in entry and exit is foreign key of stock table's column no.
I getting wrong sum values of e.qty,x.qty column(e.g displaying 600 instead of 200)

Posted 2-Jul-12 2:00am

Add a Solution

Comments

bhagirathimfs 2-Jul-12 8:08am

Can you please explain the above problem with some example???

hit91 2-Jul-12 8:16am

I am getting wrong output
values of these column e.qty and x.qty in output is wrong i.e if the sum of e.qty is 200 ,output is 400.

bhagirathimfs 2-Jul-12 8:19am

Can you give all the Tables structure?
Means:
All Column's name of all tables.

hit91 2-Jul-12 8:36am

table -'stock'
columns - no,name and available_stk

table - 'entry'
columns - no,qty

table - 'exit'
columns - no,qty

bhagirathimfs 2-Jul-12 8:42am

What do you want to do?(Actual Problem,requirment?)

1 solution

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)

Sandeep Mewara · Answer 1 · 2012-07-02T04:57:00

This might be because of the join condition you used.
s.no=e.no This might be true for more than one row leading to duplication and hence wrong result.

Example:

Table A
No Name Qty
1  ABC  10
2  XYZ  20
3  QWE  30

Table B
No Name Qty
2  XYZ  40
5  DFG  30
2  XYZ  40

Table A Left Join Table B:
A.No A.Name A.Qty B.Qty
1    ABC     10    NULL
2    XYZ     20    40
2    XYZ     20    40
3    QWE     30    NULL

If you see, above XYZ is getting duplicated based on the join. This leads to wrong sum number.

Refer: W3School LEFT Join[^]