can any one tell me why its show wrong sum?

Question

0.00/5 (No votes)

See more:

SQL> select * from employee;

NAME       EADD              EID     SALARY
---------- ---------- ---------- ----------
himanshu   sre                 1       4000
archana    jammu               2       3000
sanjana    delhi               3       2500
pooja      gng                 4       2600
bhavdeep   ynr                 5       1600

SQL> select * from project;

PNAME      DESCRIPTIO        PID       COST
---------- ---------- ---------- ----------
management manageall           1       1000
sap        spadmsn            30        300
javaadsn   adsn               40        400
.net       adsnnet            40        400


SQL> select sum(e.salary) , sum(p.cost) from employee e , project p;

SUM(E.SALARY) SUM(P.COST)
------------- -----------
        54800       10500

Posted 2-May-12 8:11am

nikhil_kumar

Add a Solution

Comments

Sergey Alexandrovich Kryukov 2-May-12 14:16pm

I don't know, but you should be charged for the difference... :-)
--SA

OriginalGriff 2-May-12 14:32pm

Answer updated

4 solutions

Solution 1

The reason is that the statement

SQL

select sum(e.salary) , sum(p.cost) from employee e , project p;

makes a Full Outer Join, due to which the rows in table employee are repeated 4 times each, hence the Sum(e.salary) = 4 * 13700 = 54800 and the rows in table project are repeated 5 times each so that the Sum(p.cost) = 5 * 2100 = 10500.

Posted 2-May-12 8:25am

VJ Reddy

Comments

Maciej Los 2-May-12 14:31pm

Good answer, my 5!

VJ Reddy 2-May-12 14:38pm

Thank you, losmac.

Solution 3

You can do it in different way too, defining column-headers and row-headres and using UNION[^] command:

SQL

SELECT 'Salary' AS [Item], SUM([Salary]) AS [Sum]
FROM employee
UNION ALL
SELECT 'Cost' AS [Item], SUM([Cost]) AS [Sum]
FROM project

Results:

Item	Sum
Salary	13700
Cost	2300

Posted 2-May-12 8:38am

Maciej Los

Updated 2-May-12 8:50am

v2

Comments

VJ Reddy 4-May-12 12:47pm

Good alternative. 5!

Maciej Los 4-May-12 13:24pm

Thank you, VJ ;)

Solution 4

It is doing a Cartesian product of the tables so each column of table 1 is multiplied by the column of table 2.
If you are using a outer join then there should be one primary key and foreign key relation which in your case is not there.
you can only try 2 different queries separately for your result.
Select sum (salary) from employee;
Select sum(cost) from project ;

Posted 2-May-12 8:53am

Muhammmad Faraz

Comments

nikhil_kumar 3-May-12 1:52am

thnx Muhammmad Faraz but is there any another way to get the sum by using only one statement?

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)

OriginalGriff · Accepted Answer · 2012-05-02T08:26:00

Solution 2

Yes.

10500 / 5 = 2300
1000 + 300 + 400 + 400 = 2300.
There are five records in the first table.

54800 / 4 = 13700
4000 + 3000 + 2500 + 2600 + 1600 = 13700
There are four records in the second table.

I'm not sure what the query should be, but that is why you are getting the result you are.

Try this:

SQL

select sum(salary) , (SELECT sum(cost) from  project) FROM employee

Posted 2-May-12 8:26am

OriginalGriff

Updated 2-May-12 8:32am

v2

Comments

Maciej Los 2-May-12 14:32pm

Good answer, my 5!

VJ Reddy 2-May-12 14:36pm

Good answer. 5!

nikhil_kumar 2-May-12 15:06pm

thnx for help but when i try this following error occur.....
SQL> select sum(salary),(select sum(cost) from project) from employee ;
select sum(salary),(select sum(cost) from project) from employee
*
ERROR at line 1:
ORA-00937: not a single-group group function

OriginalGriff 2-May-12 15:25pm

Why are you doing it twice?

nikhil_kumar 3-May-12 1:51am

bcus i want to calculate sum by using only one query.....

Maciej Los 3-May-12 3:17am

See my solution. You'll get only one result-table.