Rows that have same value in a column, sum all values in another column and display 1 row

Question

Example Table user:

    ID | USER_ID | SCORE | 
    1  |  555    |   50  | 
    2  |  555    |   10  |
    3  |  555    |   20  |
    4  |  123    |   5   |
    5  |  123    |   5   |
    6  |  999    |   30  |

The result set should be like

ID | USER_ID | SCORE | COUNT |
1  |  555    |   80  |   3   |
2  |  123    |   10  |   2   |
3  |  999    |   30  |   1   |

Is it possible to generate a sql that can return the table above, so far I can only count the rows where certain user_id appear, but don't know how to sum and show for every user ?

Answer 1

You've included a column called "ID" in both the source data and desired results, but I'm going to assume that these ID values are not related and simply represent the row or line number - otherwise the question doesn't make sense.

In which case, you can simply use:

SELECT
   USER_ID,
   SUM(SCORE) AS SCORE,
   COUNT(USER_ID) AS COUNT
FROM 
  <Table>
GROUP BY
   USER_ID

If you really want to generate the ID column as well, then how you do this depends on the database platform being used. For example on Oracle you could use the ROWNUM pseudocolumn, on SQL Server you will need to use ROW_NUMBER() function (which also works for Oracle).