Select sum two tables....

• (2 Pages)
• 1
• 2

29 Replies - 2445 Views - Last Post: 07 September 2012 - 06:43 PM

#16 luthier00

Reputation: 0
• Posts: 20
• Joined: 16-November 11

Re: Select sum two tables....

Posted 07 September 2012 - 01:28 PM

SELECT person, SUM(payment) AS Expr1
FROM (SELECT Table2.person, SUM(Table2.expr) AS exp1
FROM Table2 INNER JOIN
Table1 ON Table2.person= Table1.person
GROUP BY Table1.person) AS t
GROUP BY person

I am going to crazy... :S

#17 modi123_1

• Suitor #2

Reputation: 10676
• Posts: 41,562
• Joined: 12-June 08

Re: Select sum two tables....

Posted 07 September 2012 - 01:33 PM

No.. no.. NO! Do not do the inner join where you are doing the sum. That sum bit goes into a sub query and you join off the subquery.

#18 luthier00

Reputation: 0
• Posts: 20
• Joined: 16-November 11

Re: Select sum two tables....

Posted 07 September 2012 - 01:41 PM

May be i did not tell.. i dont know english very well.

#19 luthier00

Reputation: 0
• Posts: 20
• Joined: 16-November 11

Re: Select sum two tables....

Posted 07 September 2012 - 01:54 PM

SELECT SUM(payment) AS Total
FROM (SELECT SUM(expr) AS Expr1
FROM Table2
WHERE (person= table1.person)) AS x
GROUP BY x.person

will you help? I am going to kill myself.

#20 modi123_1

• Suitor #2

Reputation: 10676
• Posts: 41,562
• Joined: 12-June 08

Re: Select sum two tables....

Posted 07 September 2012 - 02:04 PM

Okay.. maybe if I drew it out... and colored it.

See - the blue table doesn't see the peach guts of the subquery.. it just interacts with it as if it's a table. Aka.. blue only gets to lime but doesn't see peach.

The summing happens in the peach but blue doesn't care - all it knows is there are values summed and things to join off of.

Quote

What?

#21 luthier00

Reputation: 0
• Posts: 20
• Joined: 16-November 11

Re: Select sum two tables....

Posted 07 September 2012 - 02:14 PM

SELECT b.person, SUM(b.payment) AS Expr1
FROM Table1 AS b INNER JOIN
(SELECT person, SUM(expr) AS sval_sum
FROM Table2
GROUP BY person) AS a ON a.person= b.person
GROUP BY b.person

resutls:

John 100
Mark 100
etc 100

I need john payment total and expre total. ?

#22 modi123_1

• Suitor #2

Reputation: 10676
• Posts: 41,562
• Joined: 12-June 08

Re: Select sum two tables....

Posted 07 September 2012 - 02:35 PM

Why are you summing anything on " Table1 AS b"? That table doesn't hold values to sum up.. (or at least it shouldn't).

#23 luthier00

Reputation: 0
• Posts: 20
• Joined: 16-November 11

Re: Select sum two tables....

Posted 07 September 2012 - 02:40 PM

Look my first post? Why i shouldnt sum table1?

#24 modi123_1

• Suitor #2

Reputation: 10676
• Posts: 41,562
• Joined: 12-June 08

Re: Select sum two tables....

Posted 07 September 2012 - 02:41 PM

Sorry - table 2... why is that in the subquery?

#25 luthier00

Reputation: 0
• Posts: 20
• Joined: 16-November 11

Re: Select sum two tables....

Posted 07 September 2012 - 02:54 PM

table1
Mark 10
Mark 10
John 10
John 100

table2
Mark 10
John 30
John 300
Jess 10

query result must be:

person-totalontable1-toltalontable2
Mark - 20 - 10
John - 110 - 330
Jess - 0 - 10

#26 modi123_1

• Suitor #2

Reputation: 10676
• Posts: 41,562
• Joined: 12-June 08

Re: Select sum two tables....

Posted 07 September 2012 - 02:56 PM

Those hyphens are to separate columns? then again - why are you summing up anything in table 2?

#27 luthier00

Reputation: 0
• Posts: 20
• Joined: 16-November 11

Re: Select sum two tables....

Posted 07 September 2012 - 02:59 PM

What gives me total in table2?

#28 modi123_1

• Suitor #2

Reputation: 10676
• Posts: 41,562
• Joined: 12-June 08

Re: Select sum two tables....

Posted 07 September 2012 - 03:07 PM

I don't know what that means. I saw table 2 holding a name I could join off of from table 1 and some value... a value that was different than the values held in table 1.

#29 luthier00

Reputation: 0
• Posts: 20
• Joined: 16-November 11

Re: Select sum two tables....

Posted 07 September 2012 - 03:50 PM

What is ur mission? Apperently it is not to help. May be u know, but u cant teach!

#30 modi123_1

• Suitor #2

Reputation: 10676
• Posts: 41,562
• Joined: 12-June 08

Re: Select sum two tables....

Posted 07 September 2012 - 06:43 PM

Clearly I have a different view on where the problem is.. but cest la vie and adieu.