Sum values (same column)

Question

I have this table

user_id time_completed 4 86.30887 5 57.81364 5 35.50281 5 10.00000 5 74.19355 5 31.91489 6 15.00000 6 20.50000

I need to sum all the time for each user, something like this:

user_id time_completed 4 86.30887 5 209.42489 6 35.50000

This is how I get the first table:

$result = mysql_query ("SELECT user_id,time_completed FROM `mytable` ORDER BY `mytable`.`user_id` ASC;" )

Any idea?

EDIT: What if I need to replace user_id for the name in the following table (db_users)?

id username 1 admin 2 peter 3 tom 4 user 5 joey 6 helen

EDIT2: I've modified this table (db_users) and I want country also appears in the query.

id username country 1 admin ES 2 peter IT 3 tom US 4 user GB 5 joey GE 6 helen FR

Like this:

user_id time_completed country 4 86.30887 GB 5 209.42489 GE 6 35.50000 FR

John Woo · Accepted Answer · 2012-10-19 14:55:34Z

8

you need to use SUM() which is an aggregate function and group them by their user_id

SELECT user_ID, SUM(time_COmpleted) totalTime FROM tableName GROUP BY user_ID

SQLFiddle Demo

UPDATE 1

SELECT b.username, SUM(time_COmpleted) totalTime FROM tableName a INNER JOIN db_users b ON a.user_id = b.id GROUP BY b.username

SQLFiddle Demo

answered Oct 19, 2012 at 14:18

265k70 gold badges509 silver badges500 bronze badges

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

@Manu then join the table using INNER JOIN. I've updated the answer.

@Manu where is the country coming from?

Ok, I'm writting the new question.