query for current winning streak

  • Thread starter Thread starter pat67
  • Start date Start date
P

pat67

Hi I am trying to run a query to show what a player's current and
winning streak is. I was told to use this query to start. This gives
the player and the last game he lost:

SELECT playerID, MAX(gameID) AS mgame
FROM myData
WHERE NOT isWin
GROUP BY playerID
----------------------------------------
saved as qlatestLost

Then use the below query to give the number of games after the last
loss

-------------------------------
SELECT playerID,
COUNT(qlatestLost.PlayerID) AS actualWinStreak
FROM myDataLEFT JOIN qlatestLost
ON myData.playerID = qlatestLost.playerID
AND myData.gameID > qlatestLost.mgame


Problem is this query is not working. When i run it as is I get an
error stating that playerID could come from more than one table. So
then i changed it to

SELECT myData.playerID,
COUNT(qlatestLost.PlayerID) AS actualWinStreak
FROM myDataLEFT JOIN qlatestLost
ON myData.playerID = qlatestLost.playerID
AND myData.gameID > qlatestLost.mgame

and got an error message stating that i tried to execute a query that
does not include the specified expression 'playerID' as part of an
aggregate function. so i changed it to

SELECT qlatestLost.PlayerID,
COUNT(qlatestLost.PlayerID) AS actualWinStreak
FROM myDataLEFT JOIN qlatestLost
ON myData.playerID = qlatestLost.playerID
AND myData.gameID > qlatestLost.mgame

and got the same error.

Can someone tell me what the problem is?

Thanks
 
got an error message stating that i tried to execute a query that does not
include the specified expression 'playerID' as part of an aggregate function.
What this means is that you have a 'Totals' query but did not GROUP BY the
'playerID'.
In a totals query all fields in the output must be either GROUP BY or some
function such as - First, Last, Min, Max, Sum, Count, Avg, etc.
 
include the specified expression 'playerID' as part of an aggregate function.
What this means is that you have a 'Totals' query but did not GROUP BY the
'playerID'.
In a totals query all fields in the output must be either GROUP BY or some
function such as - First, Last, Min, Max, Sum, Count, Avg, etc.

--
Build a little, test a little.














- Show quoted text -
Click to expand...

Thanks as soon as I put it as group by it worked.
 
include the specified expression 'playerID' as part of an aggregate function.
What this means is that you have a 'Totals' query but did not GROUP BY the
'playerID'.
In a totals query all fields in the output must be either GROUP BY or some
function such as - First, Last, Min, Max, Sum, Count, Avg, etc.

--
Build a little, test a little.














- Show quoted text -
Click to expand...

One quick question. First off I can't believe I am a dummy and didn't
see what was wrong. Anyway, here is my question. Along with the win
streak query I have a loss streak query as follows:

SELECT myData.PlayerID, Count(qlatestWon.PlayerID)
AS currentLossStreak
FROM myData LEFT JOIN qlatestWon
ON myData.PlayerID = qlatestWon.PlayerID
AND myData.gameID > qlatestWon.LastWonGame
GROUP BY myData.PlayerID;

The question is there are 3 players with 0 wins so when I run the last
won query

SELECT PlayerID, MAX(GameID) AS LastWonGame
FROM myData
WHERE isWin
GROUP BY PlayerID;

No gameID comes up because they haven't won any. Do I need to join the
rosters table and insert an iif statement so that the gameID for those
players shows as 0 so when I run the streak query it looks for >0
gameID?

it isn't a big big deal but i would like to know.

Thanks
 
Back
Top