Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to multiply values using SQL

Tags:

sql

oracle

Ok so I'm working on my homework and am having trouble figuring out how to multiply with SQL and how to get this to order correctly.

I am supposed to "create a query that lists players (player_name), current salary and a new (created) column that reflects a 10% increase in salary (calculated as salary * 1.1). Order the data in descending sequence by salary."

It is just a simple NHL database our teacher made up for this assignment. There are 74 players in the players table. The fields in the players table are: player_id, player_name, player_salary, team_id, and position_id.

Here is what I have so far:

SELECT player_name, player_salary, SUM(player_salary*1.1) AS NewSalary
FROM players
GROUP BY player_salary, player_name;

This way it is at least running. I believe I have to change GROUP BY to ORDER BY and use the DESC tag at the end, but that does not work. This is the output I get from running this query:

PLAYER_NAME          PLAYER_SALARY          NEWSALARY              
-------------------- ---------------------- ---------------------- 
Johan Franzen        42000                  46200                  
Brad Stuart          18000                  19800                  
Tomas Holmstrom      38000                  41800                  
Dan Cleary           10000                  11000                  
Jonathan Toews       32000                  35200                  
David Krejci         28000                  30800                  
Mike Ribeiro         10000                  11000                  
Steve Sullivan       20005                  22005.5                
Ryan Getzlaf         28000                  30800                  
Chris Stewart        18000                  19800                  
Brad Richards        10000                  11000                  
Nathan Horton        20000                  22000                  
James Neal           38000                  41800                  
Nicklas Lidstrom     44000                  48400                  
Jiri Hudler          28000                  30800                  
TJ Oshie             44000                  48400                  
Blake Comeau         10000                  11000                  
Drew Stafford        26888                  29576.8                
Brenden Morrow       10000                  11000                  
Daniel Sedin         26000                  28600                  
PA Parenteau         43000                  47300                  
Henrik Zetterberg    32000                  35200                  
Valtteri Filppula    28000                  30800                  
Tomas Kopecky        26000                  28600                  
Andrei Kostitsyn     28000                  30800                  
Marian Hossa         44000                  48400                  
Henrik Sedin         10000                  11000                  
Don Smith            20520                  22572                  
Rick Nash            15750                  17325                  
Todd Bertuzzi        43000                  47300                  
Patrick Eaves        10000                  11000                  
Mike Modano          80000                  88000                  
Alex Goligoski       28000                  30800                  
Patrick Kane         44000                  48400                  
Bobby Ryan           26000                  28600                  
Dustin Brown         10000                  11000                  
Patrick Sharp        43000                  47300                  
John-Michael Liles   10000                  11000                  
Paul Stastny         14000                  15400                  
Matt Cullen          10000                  11000                  
Martin St Louis      32000                  35200                  
Alexander Semin      28000                  30800                  
Niklas Kronwall      10000                  11000                  
John Tavares         42000                  46200                  
Matt Moulson         38000                  41800                  
Tobias Enstrom       42000                  46200                  
Matt Duchene         18000                  19800                  
Steven Stamkos       32000                  35200                  
Sidney Crosby        42000                  46200                  
Teemu Selanne        10000                  11000                  
Daniel Alfredsson    10000                  11000                  
Evgeni Malkin        10000                  11000                  
Andrew Ladd          20000                  22000                  
Corey Perry          28000                  30800                  
Adam Keefe           315000                 346500                 
Brian Rafalski       20000                  22000                  
Darren Helm          10000                  11000                  
Brandon Dubinsky     28000                  30800                  
Mark Letestu         10000                  11000                  
Loui Eriksson        20000                  22000                  
Clarke MacArthur     42000                  46200                  
Kris Letang          30000                  33000                  
Pavel Datsyuk        26000                  28600                  
James Wisniewski     32000                  35200                  
Nicklas Lidstrom     43000                  47300                  
Milan Hejduk         18000                  19800                  
Tyler Ennis          43000                  47300                  
Paul Martin          38000                  41800                  
Derek Roy            28000                  30800                  
Mikko Koivu          10000                  11000                  
Joe Pavelski         20000                  22000                  
Joe Thornton         10000                  11000                  
Phil Kessel          26000                  28600                  
Alex Ovechkin        18000                  19800                  

74 rows selected

It's probably something relatively simple that I'm overlooking but I'm not finding anything similar in the textbook for a reference point. Any help is greatly appreciated.

like image 566
Jeff Avatar asked Nov 10 '10 08:11

Jeff


1 Answers

Why use GROUP BY at all?

SELECT player_name, player_salary, player_salary*1.1 AS NewSalary
FROM players
ORDER BY player_salary DESC
like image 97
AndreKR Avatar answered Oct 27 '22 00:10

AndreKR