Avatar of Brad Brett
Brad Brett
Flag for United States of America 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.
Microsoft SQL ServerMySQL ServerPHP

Avatar of undefined
Last Comment
Brad Brett

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Cluskitt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Cluskitt

So, basically, exactly what I said, except you tabbed it and capsed it?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Brad Brett

ASKER
Thanks,