dcsimg
Results 1 to 9 of 9

Thread: Number of unique name-feature pairs

Hybrid View

  1. #1
    Join Date
    Feb 2010
    Posts
    5

    Question Number of unique name-feature pairs

    Hello all

    Basically I've got a table (we'll call it userFeaturePair) that looks something like this:
    user feature
    ------------------
    user1 featA
    user1 featA
    user1 featA
    user2 featB
    user1 featB
    user2 featA
    user3 featB
    user2 featA
    user3 featB
    user2 featA
    user4 featB

    I've also got another table (call it uniqueFeature) that looks something like this:
    feature
    --------
    featA
    featB
    featC

    I need to know how many unique users from userFeaturePair were using each feature specified in uniqueFeature so that my output would be like this:
    feature numberUniqueUsers
    -----------------------------
    featA 2
    featB 4
    featC 0

    It's easy enough to do COUNT and GROUP BY to get featA:2 featB:4, but how do I get it to count how many unique users were using featC that doesn't show up in the first table?

    Thanks!
    Errata

    P.S. I'm using MS SQL on an Access database. I don't think that makes a big difference, but I thought I would throw that out there

  2. #2
    Join Date
    Feb 2010
    Posts
    5
    No takers? I hear there will be cake

  3. #3
    Join Date
    Apr 2009
    Posts
    86
    errata, This works in DB2 LUW V9.7:
    Code:
    WITH TAB1 (USERID, FEATURE)
      AS (SELECT 'user1', 'featA' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'user1', 'featA' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'user1', 'featA' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'user2', 'featB' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'user1', 'featB' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'user2', 'featA' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'user3', 'featB' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'user2', 'featA' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'user3', 'featB' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'user2', 'featA' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'user4', 'featB' FROM SYSIBM.SYSDUMMY1
         )
       , TAB2 (FEATURE)
      AS (SELECT 'featA' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'featB' FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 'featC' FROM SYSIBM.SYSDUMMY1
         )
    SELECT TAB2.FEATURE, COUNT(DISTINCT TAB1.USERID) AS NumberUniqueUsers
    FROM TAB2
           LEFT OUTER JOIN
           TAB1
             ON TAB2.FEATURE = TAB1.FEATURE
    GROUP BY TAB2.FEATURE
    ;
    
    FEATURE NUMBERUNIQUEUSERS
    ------- -----------------
    featA                   2
    featB                   4
    featC                   0
    
      3 record(s) selected.
    The 2 Common Table Expressions are to simulate the tables and you can ignore them. The last query is what you want (with name changes).

    PS That better be Chocolate Cake...

  4. #4
    Join Date
    Feb 2010
    Posts
    5

    No Dice

    In Access 2k7 I get the following error:
    Syntax error (missing operator) in query expression `COUNT(DISTINCT [tab1 replaced with actual table name here].user)`

    Maybe my problem is using MS SQL instead of MySQL or SQL? Unfortunately in this case I am stuck with working on a .mdb file.

    Thanks, though! Any other solutions?

  5. #5
    Join Date
    Apr 2009
    Posts
    86
    errata, are you using Access as a front end to a SQL Server? If so,do you know what version SQL Server you are dealing with?

  6. #6
    Join Date
    Feb 2010
    Posts
    5
    errata, are you using Access as a front end to a SQL Server? If so,do you know what version SQL Server you are dealing with?
    This is being run locally from an .mdb file through Access without an SQL Server. I don't even have ODBC data connections set up for this in Control Panel. From what I understand, there's a Jet Database Engine somewhere that Access connects to, but I don't know where to look/how to check any version numbers for that. I would assume that it's packaged so tightly with Access that you would identify it by Access's version.

    Maybe MS SQL was the wrong acronym to use, but it sometimes doesn't like queries that will work with MySQL.

  7. #7
    Join Date
    Apr 2009
    Posts
    86
    I don't have ACCESS so I can't test this out, but this has a chance of working:
    Code:
    SELECT TAB2.FEATURE, SUM(IIF(ISNULL(TAB1.USERID),0,1) ) AS NumberUniqueUsers
    FROM TAB2
           LEFT OUTER JOIN
           (SELECT DISTINCT USERID, FEATURE
            FROM TAB1
           ) AS TAB1B
             ON TAB2.FEATURE = TAB1B.FEATURE
    GROUP BY TAB2.FEATURE
    NOTE: This uses the same table data from my previous post.

  8. #8
    Join Date
    Feb 2010
    Posts
    5

    Works!

    Quote Originally Posted by SDas View Post
    I don't have ACCESS so I can't test this out, but this has a chance of working...
    That works perfectly. Thanks for clearing this up for me!

    errata
    Attached Images Attached Images

  9. #9
    Join Date
    Apr 2009
    Posts
    86
    errata, I am glad it worked for you. ACCESS SQL isn't my strong suit because it is more convoluted (as can be seen by the two queries).

    Thanks for the Chocolate Cake. It looks good.

Tags for this Thread

Posting Permissions

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