CODEWITHSUNDEEP
mysql
Rains
Rains

Reputation: 57

Query to find the sum of the values for duplicate id in MySQL

I have a requirement :

ID            Quantity
1              5
1              4
1              2
1              4
2              1
2              2
2              3
3              1
3              3
3              2

Output required :

ID          Quantity
1              15
2              6
3              6

I have tried the below query on MySQL but unable to get appropriate result.

SELECT
a.id, sum(b.Quantity)
FROM
​table_name a
INNER JOIN
​table_name b
 ON a.id = b.id
​  ​group by
a.id, b.Quantity

Upvotes: 1

Views: 373

Answers (4)

Dmitry
Dmitry

Reputation: 304

Just remove ​b.Quantity from ​group by statement. SQL fiddle

SELECT   
a.id, sum(b.Quantity)  
FROM   
​table_name a   
INNER JOIN   
​table_name b  
 ON a.id = b.id  
​  ​group by    
a.id

Upvotes: 1

Poonam
Poonam

Reputation: 153

Use GROUP BY clause along with sum() like the query below-

select ID, sum(Quantity) FROM table_name GROUP BY ID;

Upvotes: 0

apomene
apomene

Reputation: 14389

You don't need any join. Simply try:

select id,sum(quantity) from table_name group by id

Upvotes: 0

Alex
Alex

Reputation: 17289

http://sqlfiddle.com/#!9/003625/1

SELECT id, SUM(quantity)
FROM `table_name`
GROUP BY id

Upvotes: 0

Related Questions