CODEWITHSUNDEEP
sqloracle-databaseteradata
kay am see
kay am see

Reputation: 988

how to print a sum of column in all rows in sql

lets say I have a table named "myPlayerInfo" with following rows

'player name' 'number of goals'
'john'        '10'
'martin'      '20'
'jen'         '20'

Q1. How do I write an sql query that prints a new column which is sum of 'number of goals' column ?

'player name' 'number of goals'  'Total goals by all players'
'john'        '10'               '50'
'martin'      '20'               '50'
'jen'         '20'               '50'

Q2. How do I write an sql query that prints a new column which is % of goals scored by each player with the total goals column we did in Q1. ?

'player name' 'number of goals' '% goals scored'  'Total goals by all players'
'john'        '10'              '20'                  '50'
'martin'      '20'              '40'                  '50'
'jen'         '20'              '40'                  '50'

Upvotes: 0

Views: 8145

Answers (3)

Glen Hughes
Glen Hughes

Reputation: 4832

You need to use a window function:

select
    player_name,
    number_of_goals,
    (number_of_goals * 100) / SUM(number_of_goals) OVER () AS percent_goals_scored,
    SUM([number of goals]) OVER () AS total_goals_by_all_players
from myPlayerInfo

Upvotes: 6

PhilMasteG
PhilMasteG

Reputation: 3185

You mean like SELECT name, goals, sum(goals) as sum, goals/sum(goals) as relgoals FROM table;?

Upvotes: 0

Zane Bien
Zane Bien

Reputation: 23135

You can perform a cartesian join of the total sum of all rows, derived from a subselect:

SELECT 
    a.player_name, 
    a.number_of_goals, 
    (a.number_of_goals / b.goalsum)*100 AS percent_scored,
    b.goalsum
FROM   
    myPlayerInfo a
CROSS JOIN 
    (SELECT SUM(number_of_goals) AS goalsum FROM myPlayerInfo) b

Upvotes: 3

Related Questions