Sum duplicate row values

Question

I'm trying to sum the values of rows two of which have duplicate values, the table I have is below:

Table name (Customers)

value   years   total
1           30    30
3           10    10
4           15    15
4           25    25

I would ideally like to finally have:

value  years   total
1      30      30
3      10      10
4      40      40

I've tried using SELECT DISTINCT and GROUP BY to get rid of the duplicate row, also the join in the code below isn't necessary. Regardless both commands come to no avail. Here's my code too:

SELECT DISTINCT 
  value,
  years,
  SUM(customer.years) AS total 
FROM customer 
INNER JOIN language 
  ON customer.expert=language.l_id 
GROUP BY 
  expert, 
  years;

But that produces a copy of the first table, any input welcome. Thanks!!!

Why does `SELECT value, SUM(years), SUM(total) FROM customers GROUP BY value` not work? — user1781290, Dec 04 '13 at 07:56

score 27 · Answer 1 · answered Dec 04 '13 at 07:57

27

SELECT
   value,
   SUM(years) AS years,
   SUM(total) AS total
FROM customers
GROUP BY value;

You want the sum of the years and the sum of the total, per — grouped by — value.

answered Dec 04 '13 at 07:57

SQB

3,926
2
28
49

And for those who want to group by multiple columns: http://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns – Nav Feb 13 '17 at 05:04

score 2 · Answer 2 · edited Oct 24 '17 at 17:30

2

SELECT 
  value,
  years,
  SUM(customer.years) AS total FROM (SELECT DISTINCT 
  value,
  years,
  customer.years AS total 
FROM customer 
INNER JOIN language 
  ON customer.expert=language.l_id ) as TABLECUS
GROUP BY 
  expert, 
  years;

edited Oct 24 '17 at 17:30

Vasyl Lyashkevych

1,920
2
23
38

answered Oct 24 '17 at 10:45

hegde

21
2

Sum duplicate row values

2 Answers2

Linked