How to use SUM with DISTINCT function togather-CodePudding

Is there any way by which i can SUM(value) of ColA (ColA is repeating in each ROW) based on DISTINCT(value) of ColB?

My Data Table is:

 ------- ------- ------ -------- 
| State | KEY_ID| Score| STATUS |
 ------- ------- ------ -------- 
|  ABCD |   11  |   20 |   1    |
|  ABCD |   11  |   30 |   1    |
|  ABCD |   13  |   40 |   1    |
|  DEFG |   21  |   10 |   1    |
|  DEFG |   21  |   25 |   1    |
|  DEFG |   23  |   15 |   1    |
 ------- ------- ------ --------

& wanted output like :

 ------- ------- ------ -------- 
| State | KEY_ID| Score| STATUS |
 ------- ------- ------ -------- 
|  ABCD |   11  |   50 |   1    |
|  ABCD |   13  |   40 |   1    |
|  DEFG |   21  |   35 |   1    |
|  DEFG |   23  |   15 |   1    |
 ------- ------- ------ --------

but not want to use GROUP BY function & OVER() in not working in my MySQL version 8.0.28

CodePudding user response：

You want a simple GROUP BY query to aggregate your scores. fiddle.

SELECT State, KEY_ID,
       SUM(Score) Score,
       STATUS
  FROM mytable
 GROUP BY State, KEY_ID, STATUS

CodePudding user response：

You can use GROUP_BY (https://www.w3schools.com/sql/sql_groupby.asp) on KEY_ID

like this query

SELECT State, KEY_ID, SUM(Score), STATUS FROM data_table GROUP BY KEY_ID;