Tuesday, March 6, 2012

Aggregate Function On SubQuery

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

No comments:

Post a Comment