Harika Choudary Kanikanti Harika Choudary Kanikanti - 6 months ago 17
SQL Question

Use SUM function with SQL joins

Let consider two tables as shown below:

table_a table_b
---------------- ---------------
|Name | link_id | | id | amount |
----------------- ---------------
| M | 2 | | 2 | 100 |
| N | 3 | | 3 | 20 |
| M | 2 | | 2 | 10 |
---------------- | 3 | 200 |
----------------


Now I want to get user name and sum of amount he has. The expected out put should be:

-----------------------
|Name | his_sum_amount |
------------------------
| M | 110 |
| N | 220 |
-----------------------


For that I have written a query as shown below:

select name,sum(amount) from table_a inner join table_b on table_a.link_id=table_b.id;


But above query is giving whole sum of amount, how can I get individual sum.

Answer

In most databases your query would generate an error, because of name in the select.

You are simply missing group by:

  select name, sum(amount)
  from table_a inner join
       table_b
       on table_a.link_id = table_b.id
  group by name;