I am working on a view in SQL Server 2005.
I am trying to get a list of the number of sessions each user had by user. I tried doing it this way, but
SELECT userid, MAX
((SELECT COUNT(DISTINCT sessionId) AS SESSIONCOUNT
FROM dbo.Sessions AS OD
HAVING (sessionId = O.sessionId))) AS MAXSESSION
FROM dbo.Sessions AS O
GROUP BY userid
but it throws an error 'Cannot perform an aggregate function on an expression containing an aggregate or subquery.'
Is there an elegant solution for this?
Thanks,Hi Doug
Maybe I missed something but why does this not do it:
SELECT userid, COUNT(DISTINCT sessionId) AS SESSIONCOUNT
FROM dbo.Sessions
GROUP BY userid
?
The Max isn't necessary as the sub-select should return one value per user. Remove that and the above is equivelent.
HTH
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment