how to write this query in sql

how to write this query in sql : For every player that has played more than two games, list the player name, total amount of winnings and number of games played for each player". The result should be sorted by the winnings in descending order.

and i have in player table these attributes: playerId,playerName,age and in games table these attrubites: gameId,playerId,results note the results attrubie is filled either by (f开发者_如何学编程irst or second or third or,..,or no show) the winner is the one who has the result= first

this is my weak query i didn't got the right answer ,but that all what i can do . any idea

select playerName,count(*),count(*)
 from games,player
 where games.playerId=player.playerId
 group by games.results

You want to look into GROUP BY and HAVING in conjunction with COUNT. Something like this would probably do (untested):

SELECT
   p.playerName
   ,COUNT(g.*)
   ,SUM(g.Winnings)  -- you didn't name this column
FROM
   games g
   INNER JOIN ON g.playerId = p.playerId
WHERE
   g.results = 1     -- whatever indicates this player was the winner
GROUP BY
   p.playerName
HAVING
   COUNT(g.*) > 2

*Try this (pretty much as you said it in English...
(if "winnings" is amount won in the game), then:

  Select playerName, count(*) Games,  -- Number of game records per player
      Sum(g.Winnings) Winnings        -- Sum of a Winnings attribute (dollars ??)
  from player p Join Games g          -- from the two tables
    On g.PlayerId = p.PlayerId        -- connected using PlayerId
  Group by p.playerName               -- Output in one row per Player
  Having Count(*) > 2                 -- only show players w/more than 2 games
  Order By Sum(g.Winnings)            -- sort the rows based on Player Winnings

if by "Winnings" you mean the number of games won, then...

  Select playerName, Count(*) Games, -- Number of game records per player
      Sum(Case g.WonTheGame          -- or whatever attribute is used
          When 'Y' Then 1            -- to specify that player won
          Else 0 End) Wins           -- Output in one row per Player
  From player p Join Games g         -- from the two tables
    On g.PlayerId = p.PlayerId       -- connected using PlayerId
  Group by p.playerName              -- Output in one row per Player
  Having Count(*) > 2                -- only show players w/more than 2 games
  Order By Sum(Case g.WonTheGame     -- Sort by Number of games Won
          When 'Y' Then 1 
          Else 0 End)

Try this :

SELECT playerName, COUNT(g.PlayerID) as NumberOfPlays
 FROM games g ,player p 
 WHERE g.playerId=p.playerId 
 GROUP BY g.PlayerID
 HAVING COUNT(g.PlayerID) > 1
 ORDER BY g.results DESC

SELECT - the data you want to display
FROM - the tables
WHERE - both IDs match each other
GROUP BY - Games PlayerID, so all the counts are correct
HAVING - Make sure they played more then one game
ORDER BY - Order the results the way you want them.

it's tough to glean exactly what you need from your question but try something like this:

select playerName, count(*)
 from games g
 join player p ON g.playerId = p.playerId
 group by playerName
 having count(*) > 2
 order by games.results DESC

select
playerName,
sum(if(games.result = 'first',1,0)) as wins,
count(*) as gamesPlayed
from player
join games on games.playerId = player.playerId
group by games.results
having count(*) > 2
order by count(*) desc;