Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    18

    Unanswered: 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,
    -Doug Picanzi

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Doug

    Maybe I missed something but why does this not do it:

    Code:
    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
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •