Results 1 to 3 of 3

Thread: Outer Join?

  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: Outer Join?

    Hi

    I just need some kewords and pointing in the right direction - i have a simple sql script and wish to output more data, just not sure what to use. So far i have this:

    Code:
    SELECT R.RoleName, COUNT(*) AS Clients
    FROM aspnet_Membership AS M
    INNER JOIN aspnet_UsersInRoles AS U ON U.UserId = M.[UserId]
    INNER JOIN aspnet_Roles AS R ON R.RoleId = U.RoleId
    WHERE R.RoleName <> 'client_generic'
    GROUP BY R.RoleName
    This outputs a simple table which counts the number of users in each group:

    RoleName Clients
    client_bm 8
    client_cs 1
    client_pl 52
    client_pp 1

    My question is, how can i add another column to the same output? For example, if i wanted to count the number of clients which signed up in a particular month?

    thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT R.RoleName
         , COUNT(*) AS Clients
         , COUNT(CASE WHEN signedup >= '2009-10-01'
                       AND signedup  < '2009-11-01'
                      THEN 'x' ELSE NULL END) AS signedup_october
      FROM aspnet_Membership AS M
    INNER 
      JOIN aspnet_UsersInRoles AS U 
        ON U.UserId = M.UserId
    INNER 
      JOIN aspnet_Roles AS R 
        ON R.RoleId = U.RoleId
     WHERE R.RoleName <> 'client_generic'
    GROUP 
        BY R.RoleName
    Last edited by r937; 01-11-10 at 20:29.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    awesome, thanks rudy!

Posting Permissions

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