Reputation: 319

mySQL - SUM of column

I have 2 tables

table 1

id product
1  P1
2  P2

table 2

id amount product_t1
1  100    P1
2  200    P1
3  300    P2
4  400    P1
5  500    P2

I want my output to be:

product totalAmount(sum of amount)
P1      700
P2      800

EDIT: Here is my query so far

SELECT T1.product, SUM(T2.amount)
FROM table1 T1
INNER JOIN table2 T2
ON T1.product = T2.product_t1

Thanks!

Upvotes: 1

Answers (4)

Reputation: 1089

Try to play with GROUP BY and AS

 SELECT product_t1 as product, SUM(amount) AS totalAmount FROM table2
 GROUP BY product

https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html

OR add to your code group by

 SELECT T1.product, SUM(T2.amount) 
 FROM table1 T1 
 INNER JOIN table2 T2 ONT1.product = T2.product_t1 

 Gruop by T1.product

Upvotes: 0

Reputation: 2878

 SELECT T1.product, SUM(T2.amount)
 FROM table1 T1
 INNER JOIN table2 T2
 ON T1.product = T2.product_t1
 GROUP BY product T1.product

Use the GROUP BY tag . Group by helps to group your result via value of product

Upvotes: 1

Reputation: 111

you should use group by

 SELECT T1.product, SUM(T2.amount)
 FROM table1 T1
 INNER JOIN table2 T2
 ON T1.product = T2.product_t1
 GROUP BY product

Upvotes: 1

Reputation: 32158

Since you're not using foreign keys you don't even need the table 1 for the desired result.

SELECT
    product_t1 AS product,
    SUM(amount) AS totalAmount
FROM table2
GROUP BY product_t1

What you're missing is the GROUP BY statement in order to get a separate row for each individual product

Upvotes: 1