SQL Count unique objects defined by parameters

Question

I have a table with:

id | parameter
 1 | A
 1 | B
 2 | A
 3 | A
 3 | B

That represent objects defined with the values as:

 1 -> A,B
 2 -> A
 3 -> A,B

I want to count the number of objects with different parameters using a SQL query, so in this case it would be 2 unique objects as 1 and 3 have the same parameters.

There is no constraint on the number of parameters, there can be 0, or any other number.

The database is a Microsoft SQL Server 2000. But I do not mind knowing the solution for other databases.

Answer 1

If I understand correctly, you want the number of distinct combinations of parameters per id represented in your table, possibly with the number of entities exhibiting each of those distinct combinations.

I can't speak for SQL Server, but under MySQL you could do something like this:

  SELECT parameter_set, COUNT(*) AS entity_count
    FROM (
          -- Here we "flatten" the different parameter combinations per id
             SELECT id,
                    GROUP_CONCAT(parameter ORDER BY parameter) AS parameter_set
               FROM tbl
           GROUP BY id
         ) d
GROUP BY parameter_set;

which will give you this:

 parameter_set | entity_count
---------------+--------------
 A,B           |            2   -- two entities have params A, B
 A             |            1   -- one entity has param A

and SELECT COUNT(DISTINCT parameter_set FROM (... flattening query ...)) d will give you the number of distinct parameter sets.