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

Table Scores

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

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

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

CodePudding user response：

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;

CodePudding user response：

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.

CodePudding user response：

You can use rollup

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

CodePudding user response：

You didn't specify Postgres vs SQL Server, but I had Postgres on hand.

This works:

CREATE TABLE tmp_summit (player TEXT, score int);
INSERT INTO tmp_summit (player, score) VALUES ('tim', 30);
INSERT INTO tmp_summit (player, score) VALUES ('jim', 40);
INSERT INTO tmp_summit (player, score) VALUES ('slim', 50);

SELECT 
    player
  , score
  , SUM(score) OVER () AS all_sum
FROM tmp_summit