Select sum two tables....

  • (2 Pages)
  • +
  • 1
  • 2

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

#16 luthier00  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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
Was This Post Helpful? 0
  • +
  • -

#17 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9195
  • View blog
  • Posts: 34,516
  • 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.
Was This Post Helpful? 0
  • +
  • -

#18 luthier00  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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. :/
Was This Post Helpful? 0
  • +
  • -

#19 luthier00  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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.

Is your teaching way?
Was This Post Helpful? 0
  • +
  • -

#20 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9195
  • View blog
  • Posts: 34,516
  • 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.

Attached Image

Also I would advocate not taking your life.

Quote

Is your teaching way?

What?
Was This Post Helpful? 1
  • +
  • -

#21 luthier00  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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. ?
Was This Post Helpful? 0
  • +
  • -

#22 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9195
  • View blog
  • Posts: 34,516
  • 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).
Was This Post Helpful? 0
  • +
  • -

#23 luthier00  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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?
Was This Post Helpful? 0
  • +
  • -

#24 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9195
  • View blog
  • Posts: 34,516
  • 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?
Was This Post Helpful? 0
  • +
  • -

#25 luthier00  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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
Was This Post Helpful? 0
  • +
  • -

#26 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9195
  • View blog
  • Posts: 34,516
  • 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?
Was This Post Helpful? 0
  • +
  • -

#27 luthier00  Icon User is offline

  • New D.I.C Head

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

Re: Select sum two tables....

Posted 07 September 2012 - 02:59 PM

What gives me total in table2?
Was This Post Helpful? 0
  • +
  • -

#28 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9195
  • View blog
  • Posts: 34,516
  • 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.
Was This Post Helpful? 0
  • +
  • -

#29 luthier00  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • 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!
Was This Post Helpful? 0
  • +
  • -

#30 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9195
  • View blog
  • Posts: 34,516
  • 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.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2