I am trying a self join that isn't

[struth]

I am trying a self join that isn't quite coming off because it involves adding up a users game points and counting the nuber of times that user has played that game. I can get Count() or Sum() working OK but not together. I've tried various ways of bracketing them but nothing is coming off. Frustrating day today :~(

SELECT Sum(Points) AS SSP
FROM BigGame
WHERE BigGameOK = varOK AND UserName = 'varUser'

Can you help me out... thanks

Struth

"Its life Jim, but not as we know it."

 

[JuanitaC]

Well, I don't have table that I can test this on, but maybe try this:

SELECT Sum(Points) AS SSP,
(SELECT Count(Points)
FROM BigGame
WHERE BigGameOK = varOK and UserName='varUser'
) as PointCount
FROM BigGame
WHERE BigGameOK = varOK AND UserName = 'varUser'

 

[struth]

I thought you had it there ...

but I got the too few parameters error. I'll keep messing about til I crack it .... or it cracks me.

Thanks
Struth "It's life Jim, but not as we know it!"

 

[struth]

Nailed it! But don't ask me to explain it - total trial and error:

SELECT * FROM (SELECT (SELECT COUNT (*) FROM BigGame WHERE UserName = 'varUser' AND BigGameOK=varOK) AS PointCount, (SELECT Sum (Points)
FROM BigGame
WHERE UserName = 'varUser' AND BigGameOK=varOK) AS SSP FROM BigGame)

Think i'll go and have a wee drink now to celebrate.

Thanks for helping to set me on the right path.

Struth "It's life Jim, but not as we know it!"