asked on

Summation SQL Statement

If I have the following table:

scores_log
-------------------------------------------------------------
userid matches score
-------------------------------------------------------------
1 10 500
2 3 300
8 1 800
1 2 200
8 5 600
-------------------------------------------------------------
As you see this is log records and userid can be duplicated multiple times.

What SQL statement statement should I use If I want to get the total summation of matches and score for each user (summation should include duplicated userid)?

Here is what I am trying to get using SQL statement:
-------------------------------------------------------------
userid matches score
-------------------------------------------------------------
1 12 700
2 3 300
8 6 1400
-------------------------------------------------------------

As you can see the duplicated rows are summated.

I am using MySQL.

ASKER CERTIFIED SOLUTION

Cluskitt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gerry Bartley

SELECT USERID, Sum(MATCHES) AS [Match Total], Sum(SCORE) AS [Score Total]
FROM scores_log
GROUP BY USERID
order by userid

Open in new window

Forget this. Just saw Cluskitt's post

Brad Brett

ASKER

I forgot to mention, what in the case that I want to get username instead of userid, so the result become:
-----------------------------------------------------
username matches score
-----------------------------------------------------
john 12 700
william 3 300
brad 6 1400

so basically, the SQL statement should summarize matches and score and convert userid to username by laying on the table "users".

Table "users":
-----------------------------------------------------
id username
-----------------------------------------------------
1 john
2 william
8 brad

SOLUTION

Cluskitt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anuradha Goli

SELECT   USERNAME, 
               SUM(MATCHES) AS MATCHES, 
               SUM(SCORE) AS SCORES
FROM     SCORES_LOG 
INNER 
JOIN        USERS 
ON          USERID = ID
GROUP   BY USERNAME

Open in new window

Cluskitt

So, basically, exactly what I said, except you tabbed it and capsed it?

SOLUTION

Theo Kouwenhoven

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brad Brett

ASKER

Thanks,