[luthier00]

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.

[modi123_1]

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.

[luthier00]

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.

[modi123_1]

Where's your attempts? I am not just going to give out the query without some education going occurring!

[luthier00]

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....

[modi123_1]

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

[luthier00]

modi123_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?

[modi123_1]

Excuse me?

[luthier00]

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.

[modi123_1]

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.

[luthier00]

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.

[modi123_1]

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!

[DarenR]

luthier00, 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

[luthier00]

Thank you and i am sorry to wasting ur times... But i did not understand. Dont bother.

[modi123_1]

Okey dokey.. Have a good one.