Summation SQL Statement

Mohamed Abowarda
Mohamed Abowarda used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SELECT userid, SUM(matches) AS matches, SUM(score) AS score
FROM scores_log
GROUP BY userid
Gerry BartleySoftware Engineer

Commented:
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
Mohamed AbowardaSenior Software Engineer

Author

Commented:
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
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

SELECT username, SUM(matches) AS matches, SUM(score) AS score
FROM scores_log INNER JOIN users ON userid=id
GROUP BY username
Anuradha GoliSystems Development / Support Specialist

Commented:
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

So, basically, exactly what I said, except you tabbed it and capsed it?
Theo KouwenhovenApplication Consultant
Commented:
SELECT   USERNAME,
               SUM(MATCHES) AS MATCHES,
               SUM(SCORE) AS SCORES
FROM     SCORES_LOG, USERS
WHERE   USERID = ID
GROUP   BY USERNAME
Mohamed AbowardaSenior Software Engineer

Author

Commented:
Thanks,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial