Number of unique name-feature pairs

[errata]

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

[errata]

No takers? I hear there will be cake

[SDas]

errata, This works in DB2 LUW V9.7:

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...

[errata]

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?

[SDas]

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?

[errata]

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.

[SDas]

I don't have ACCESS so I can't test this out, but this has a chance of working:

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.

[errata]

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

[SDas]

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.