CODEWITHSUNDEEP
sqlmysqlaggregate-functions
Stive
Stive

Reputation: 73

how to create this query

how to create a query if i need to include two aggregate function in select row and per each function i need different group by and where conditions in my example i need to returns the playerName, and how many the player win the this can be checked if the results in table game result= first, and how many times he played

but do not know how to deal with two aggregate functions .

simply i want to join the result of this 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

the set of attributes for table game are playerId , result the set of attributes for table player are playerName,playerId

any idea???

Upvotes: 4

Views: 111

Answers (2)

a1ex07
a1ex07

Reputation: 37384

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

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

Then, on client side, find records with result equals 'first' and and result is null(which is #games played).

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332661

Use:

  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

Upvotes: 3

Related Questions