Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Aug 10th, 2012  |  syntax: None  |  size: 1.01 KB  |  hits: 8  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. how to print a sum of column in all rows in sql
  2. 'player name' 'number of goals'
  3. 'john'        '10'
  4. 'martin'      '20'
  5. 'jen'         '20'
  6.        
  7. 'player name' 'number of goals'  'Total goals by all players'
  8. 'john'        '10'               '50'
  9. 'martin'      '20'               '50'
  10. 'jen'         '20'               '50'
  11.        
  12. 'player name' 'number of goals' '% goals scored'  'Total goals by all players'
  13. 'john'        '10'              '20'                  '50'
  14. 'martin'      '20'              '40'                  '50'
  15. 'jen'         '20'              '40'                  '50'
  16.        
  17. SELECT
  18.     a.player_name,
  19.     a.number_of_goals,
  20.     (a.number_of_goals / b.goalsum)*100 AS percent_scored,
  21.     b.goalsum
  22. FROM  
  23.     myPlayerInfo a
  24. CROSS JOIN
  25.     (SELECT SUM(number_of_goals) AS goalsum FROM myPlayerInfo) b
  26.        
  27. select
  28.     player_name,
  29.     number_of_goals,
  30.     (number_of_goals * 100) / SUM(number_of_goals) OVER () AS percent_goals_scored,
  31.     SUM([number of goals]) OVER () AS total_goals_by_all_players
  32. from myPlayerInfo
create a new version of this paste RAW Paste Data