Click here to Skip to main content
14,920,001 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi all

i have two tables like:
name  amont  id
jhon  2000    1
neo   3000    2
jhon  1000    1
neo   2000    2


and second table:
name   id 
jhon    1
neo     2
rams    3
raj     4
anna    5


I tried sql like this:
select table1.name,sum(amount),table2.id from table1,table2 group by table2.id

this query returns:
jhon   8000    1
neo    8000    2
rams   8000    3
raj    8000    4
anna   8000    5


but i need to get total amount who paid in the above jhon and neo paid, so here i get values like
jhon   3000
neeo   5000
rams   0
raj    0
anna   0


can any one please help me

thank you in advance
Posted
Updated 21-Apr-11 21:14pm
v2
Comments
Kim Togo 22-Apr-11 3:15am
   
Edit for better readability.

SQL
SELECT table1.name,  IF( isnull( sum( amount ) ) , 0, sum( amount ) )  , table2.id
FROM table1 RIGHT JOIN table2 ON table1.id = table2.id
GROUP BY table2.id



Use this query. You have forget to link both tables(Joins)

Regards
Ankit
   
v3
Comments
Kim Togo 22-Apr-11 3:17am
   
Yes. It seems that OP has for got to JOIN table1 and table2.
tulasiram3975 22-Apr-11 3:18am
   
If i Do id=id then i get only Jhon And neo value and their total. But I Want Not Equal ID's Also.
I Want Like This
jhon 3000
neeo 5000
rams 0
raj 0
anna 0
Ankit Rajput 22-Apr-11 3:55am
   
For that you need to use Outer Join
Ankit Rajput 22-Apr-11 4:09am
   
I have updated the query. Now the result will be same as desired.
tulasiram3975 22-Apr-11 4:33am
   
Thank You For valuable Answer...
Ankit Rajput 22-Apr-11 5:25am
   
most welcome
something like this?

SELECT  tb1.name,SUM(CASE WHEN tb1.id=tb2.id THEN amount ELSE 0 END) AS "Total"
FROM t1 AS tb1, t2 AS tb2
GROUP BY tb1.name


i wonder why would you include name in the first table..
   
Comments
Ankit Rajput 22-Apr-11 3:54am
   
thats not an issue.
You can use it from table1
Ankit Rajput 22-Apr-11 6:47am
   
Downvoted: You should ask clarification in comment. Please do not ask as New Solution.
Try like that

select TABLE2.id, isnull( Sum(amount),0) from TABLE2
LEFT outer join table1 on TABLE2.ID = TABLE1.ID
group by TABLE2.ID
   
Comments
Ankit Rajput 22-Apr-11 4:10am
   
downvoting: because it will not work in MySql. Query provided will work in SQl Server.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900