Use SUM function with SQL joins

Question

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.

I guess you meant 'M','N', not '2','3'

Strawberry
– Strawberry

2016-06-06 13:13:57 +00:00
Commented Jun 6, 2016 at 13:13 — Strawberry
– Strawberry, Commented Jun 6, 2016 at 13:13

Gordon Linoff · Accepted Answer · 2016-06-06 12:20:48Z

3

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;

answered Jun 6, 2016 at 12:20

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt · Accepted Answer · 2016-06-06 13:35:36Z

0

Use GROUP BY to SUM by name.

SELECT a.name, SUM(b.amount) AS his_sum_amount
FROM table_a a 
INNER JOIN table_b b ON a.link_id = b.id
GROUP BY a.name

Output

name  his_sum_amount
M     220
N     220

SQL Fiddle: http://sqlfiddle.com/#!9/00085/1/0

answered Jun 6, 2016 at 13:35

Matt

15.1k28 gold badges109 silver badges161 bronze badges

Collectives™ on Stack Overflow

Use SUM function with SQL joins

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related