How to create this request

Question

how to create a query, if I need to include two aggregated functions in the selection line, and for each function I need a different group, and where are the conditions in my example, I need to return the name of the player, and how many players will win, this can be checked if the result is in the game table result = first, and how many times has he played

but they don’t know how to handle the two aggregate functions.

just want to join the result of these two queries

1.

select playeName,count(*) from player,game where player.playerId=game.playerId and result="first" group by game.playerId 

2.

 select count(*) from game, player where game.playerId=player.playerId group by game.playerId 

set of attributes for the board game playerId, result set of attributes for the board player Playername, playerId

any idea ???

Answer 1

Using:

  SELECT p.playername, SUM(CASE WHEN g.result = 'first' THEN 1 ELSE 0 END), COUNT(*) FROM PLAYER p JOIN GAME g ON g.playerid = p.playerid GROUP BY p.playername 

Answer 2

Along with the solutions proposed by OMG Ponies and Bnjmn , you can also get the desired results using WITH ROLLUP

 select result, count(*) from game, player where game.playerId=player.playerId group by game.playerId, result WITH ROLLUP 

Then, on the client side, searching for records with the result is 'first', and the result is null (which is played by #games).