mysql query for getting details

Question

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 21-Apr-11 20:58pm

tulasiram3975

Updated 21-Apr-11 21:14pm

Kim Togo

v2

Add a Solution

Comments

Kim Togo 22-Apr-11 3:15am

Edit for better readability.

3 solutions

Solution 1

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

Posted 21-Apr-11 21:12pm

Ankit Rajput

Updated 21-Apr-11 22:08pm

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

Solution 3

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

Posted 21-Apr-11 21:59pm

nit_singh

Comments

Ankit Rajput 22-Apr-11 4:10am

downvoting: because it will not work in MySql. Query provided will work in SQl Server.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

BlackJack99 · Accepted Answer · 2011-04-21T21:47:00

Solution 2

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

Posted 21-Apr-11 21:47pm

BlackJack99

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.