CODEWITHSUNDEEP
mysqlsql
Priyansh
Priyansh

Reputation: 1248

Get sum of all the row in second column in to the third column

Table Scores

Id  Player  Score
1   John    134
2   Tom     146
3   Lucy    20
4   Tom     118
5   Tom     102
6   Lucy    90
7   Lucy    34
8   John    122

SELECT player, SUM(score) as sum_score
FROM game
GROUP BY player;

Player  Score
John    256
Tom     366
Lucy    144

What I am looking for is sum of all the values from second column, so that I can further find out the percentage.

256+366+144=766

enter image description here

What changes could be made to the above query to achieve the desired results, are there any windows function which can be used here?

Upvotes: 0

Views: 194

Answers (3)

Tim Martin
Tim Martin

Reputation: 2509

So generally I would recommend doing this in application code if at all possible since that will be easier to follow. But you could use a subquery to find the total sum and include that as a column

SELECT 
  player, 
  SUM(score) AS player_score, 
  (SELECT SUM(score) FROM player) AS total_score
FROM
  game
GROUP BY
  player

I can't speak for all databases, but most databases should run the subquery only once. I can confirm that since it's an uncorrelated subquery, MySQL will only run the subquery once. It is a bit of double work still though because we're running through the player table twice.

Alternatively, you could use window functions, but we'll need to make sure we select DISTINCT or we'll get duplicate rows in the set.

SELECT DISTINCT
  player,
  SUM(score) OVER (PARTITION BY player) AS player_score,
  SUM(score) OVER () AS total_score
FROM
  game
;

Personally, I like the window functions better, but they are not always supported depending on your DBMS (or version). Also, I've found that other people tend to get a bit more confused by window functions so depending on the expertise of the other people who may be reading the SQL, you may want to switch up which strategy you use.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can combine aggregation with window functions:

SELECT player, SUM(score) as sum_score,
       SUM(SUM(score)) OVER () as total_score
FROM game
GROUP BY player;

Upvotes: 2

Stu
Stu

Reputation: 32609

You can use rollup

select Coalesce(player,'Total') Player, Sum(score) Sum_Score
from game
group by player
with rollup

Upvotes: 0

Related Questions