Nested Count
in [Programming SQL]
|
Prev: How to create SQL logins in FIPS compliant environment ?
Next: Automate Scripting of Jobs to a flat file
From: Negel on 28 Jan 2010 06:02 Hi all. I have got 3 tables. GamePlayer - GameMatchId, GamePlayerName, Results GameRecord - GameMatchId, Email, GamePlayerSelection User - Id, Email, FullName, DOB i'm trying to do a 'nested count', where if GamePlayer.Results = 'Win', it will do a count and give me the results as WIN and if GamePlayer.Results = 'Lose', it will do a count and give me the results as LOSE The codes are: SELECT [User].FullName (SELECT count(GamePlayer.Results) AS Results FROM GamePlayer WHERE GameRecord.Email = User .Email) FROM GamePlayer INNER JOIN GameRecord ON GamePlayer.GameMatchId = GameRecord.GameMatchId INNER JOIN [User] ON GameRecord.Email = [User].Email GROUP BY [User].FullName Thanks Alot Submitted via EggHeadCafe - Software Developer Portal of Choice Share Outlook Express Message Store http://www.eggheadcafe.com/tutorials/aspnet/c80aafb8-bea5-4817-9d9e-14b1c7348dc9/share-outlook-express-mes.aspx
From: Uri Dimant on 28 Jan 2010 06:25
Hi See if CASE expression help you SELECT COUNT(CASE WHEN Results = 'Win' THEN 1 END) Win ........................................ FROM Table <Negel Neo> wrote in message news:20101286159kyokusho(a)hotmail.com... > Hi all. > > I have got 3 tables. > > GamePlayer - GameMatchId, GamePlayerName, Results > GameRecord - GameMatchId, Email, GamePlayerSelection > User - Id, Email, FullName, DOB > > i'm trying to do a 'nested count', > where if GamePlayer.Results = 'Win', it will do a count and give me the > results as WIN > and if GamePlayer.Results = 'Lose', it will do a count and give me the > results as LOSE > > The codes are: > SELECT [User].FullName > (SELECT count(GamePlayer.Results) AS Results > FROM GamePlayer > WHERE GameRecord.Email = User .Email) > FROM GamePlayer INNER JOIN > GameRecord ON GamePlayer.GameMatchId = GameRecord.GameMatchId INNER JOIN > [User] ON GameRecord.Email = [User].Email > GROUP BY [User].FullName > > > Thanks Alot > > > Submitted via EggHeadCafe - Software Developer Portal of Choice > Share Outlook Express Message Store > http://www.eggheadcafe.com/tutorials/aspnet/c80aafb8-bea5-4817-9d9e-14b1c7348dc9/share-outlook-express-mes.aspx |