Select sum two tables....

  • (2 Pages)
  • +
  • 1
  • 2

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

#1 luthier00  Icon User is offline

  • New D.I.C Head

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

Select sum two tables....

Posted 07 September 2012 - 11:46 AM

I just need a simple query but i couldn't handle it. I have two tables in my mssql database

table1 (person, payment) (payment data type int)

Mark 100
Mark 150
John 100
Simith 50

table2 (person, expr) (expr type int)
Mark 50
John 100
Celin 200

I want to output result like this
(person,payment total, expr total)
Mark, 250, 50
John, 100, 100
Simith, 50, 0
Celin 0, 200

thanks for all.

Is This A Good Question/Topic? 0
  • +

Replies To: Select sum two tables....

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9265
  • View blog
  • Posts: 34,754
  • Joined: 12-June 08

Re: Select sum two tables....

Posted 07 September 2012 - 11:57 AM

On table1 I would do a select - with a group by person's name - and a sum of payment... and then join that with the table2 to grab the expr total.
Was This Post Helpful? 0
  • +
  • -

#3 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 - 12:05 PM

Yes i know that... I couldnt write query.

I tried and searched some query samples.... Resutls are wrong or give error...

"select person, sum(payment) from table1 group by person" not enough

"select table1.person, sum(table1.paymen) as pays, sum(table2.expr) as exp from table1, table2 where table1.person=table2.person group by table1.person" all results are same....

I also tried full join, left, right etc. I coudnt, so i asked help... It must be very simple but not for me.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9265
  • View blog
  • Posts: 34,754
  • Joined: 12-June 08

Re: Select sum two tables....

Posted 07 September 2012 - 12:07 PM

Where's your attempts? I am not just going to give out the query without some education going occurring!
Was This Post Helpful? 0
  • +
  • -

#5 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 - 12:15 PM

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

This gives:

200 200 Mark
200 200 John
.
.
.

I tried like this a lof ot query....
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9265
  • View blog
  • Posts: 34,754
  • Joined: 12-June 08

Re: Select sum two tables....

Posted 07 September 2012 - 12:16 PM

There's your problem... don't combine/join the query you are doing the sum/group by on... do that AFTER the fact.
Was This Post Helpful? 0
  • +
  • -

#7 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 - 12:19 PM

View Postmodi123_1, on 07 September 2012 - 12:16 PM, said:

There's your problem... don't combine/join the query you are doing the sum/group by on... do that AFTER the fact.



Is your help?
Was This Post Helpful? 0
  • +
  • -

#8 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9265
  • View blog
  • Posts: 34,754
  • Joined: 12-June 08

Re: Select sum two tables....

Posted 07 September 2012 - 12:20 PM

Excuse me?
Was This Post Helpful? 0
  • +
  • -

#9 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 - 12:23 PM

I dont know and i can not build query sentence. So i asked help... ur post not help to me.

What did i learn? Nothing.
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9265
  • View blog
  • Posts: 34,754
  • Joined: 12-June 08

Re: Select sum two tables....

Posted 07 September 2012 - 12:34 PM

yeah I am getting there chief.. I am trying to write an example that isn't just the code for your problem.

The whole idea is subqueries... write a select query - *INSIDE parentheses*, give it an alias (OUTSIDE the parans), and you can use that like a regular table.

http://msdn.microsof...sql.105%29.aspx


  select *
  from dbo.Table_fk_1 b  -- regular table with alias.
  join -- standard join
 ( -- start the subquery
	SELECT tb1Key -- the id key we want to use
    ,sum(sVal) as sval_sum -- the sum
	FROM dbo.Table_fk_2  -- the second table we want to sum up on
	group by tb1Key  -- but sum by grouping on the id key
  ) a -- give the sub query an alias
    on a.tb1Key = b.lID -- tell what to join on.


Was This Post Helpful? 0
  • +
  • -

#11 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 - 12:57 PM

I am so sorry but i cant understand ur sample and other samples(100s)...

what is "from dbo.Table_fk_1 b"? where is sum table1 value and sum table2 values? I dont understand.

U can say idiot but i dont know sql very well and i dont understand join... Can u write query for my tables or not?

(I wish to learn, but i need a solution quickly , is it too hard to write ten words for u?... if it is, ok! Dont bother urself, because i will not understand your recipies)

thanks.
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9265
  • View blog
  • Posts: 34,754
  • Joined: 12-June 08

Re: Select sum two tables....

Posted 07 September 2012 - 01:05 PM

Quote

what is "from dbo.Table_fk_1 b"?

That is a table name.. you know in your typical "FROM" portion of of a sql select statement?


Quote

where is sum table1 value and sum table2 values? I dont understand.

What? You wanted to have the sum of one table... and then join that into the second table.

Here's the portion of the query that does the sum... I am using that entire query as in a SUBQUERY.. which has SQL treat everything in between the parenthesis as if it was a table.
SELECT tb1Key -- the id key we want to use
,sum(sVal) as sval_sum -- the sum
FROM dbo.Table_fk_2  -- the second table we want to sum up on
group by tb1Key  -- but sum by grouping on the id key




Quote

U can say idiot but i dont know sql very well and i dont understand join... Can u write query for my tables or not?

Yes, I can write it but I won't... Just giving you the code deprives you of this learning opportunity. Heck that query I gave a few posts up basically does it for you with a few column and table name changes..

Quote

(I wish to learn, but i need a solution quickly , is it too hard to write ten words for u?... if it is, ok! Dont bother urself, because i will not understand your recipies)

thanks.

Clearly I can write more than ten words! ;)
Was This Post Helpful? 0
  • +
  • -

#13 DarenR  Icon User is online

  • D.I.C Lover

Reputation: 469
  • View blog
  • Posts: 3,196
  • Joined: 12-January 10

Re: Select sum two tables....

Posted 07 September 2012 - 01:08 PM

View Postluthier00, on 07 September 2012 - 03:57 PM, said:

I am so sorry but i cant understand ur sample and other samples(100s)...

what is "from dbo.Table_fk_1 b"? where is sum table1 value and sum table2 values? I dont understand.



from dbo.Table_fk_1 b is a table name and the b is the table name alias. so instead of writing
from dbo.Table_fk_1.id, from dbo.Table_fk_1.exper, and so on the b takes place of the table name.
like

from  b.id, b.exper and so on

Was This Post Helpful? 0
  • +
  • -

#14 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:13 PM

Thank you and i am sorry to wasting ur times... But i did not understand. Dont bother.
Was This Post Helpful? 0
  • +
  • -

#15 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9265
  • View blog
  • Posts: 34,754
  • Joined: 12-June 08

Re: Select sum two tables....

Posted 07 September 2012 - 01:14 PM

Okey dokey.. Have a good one.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2