-- A table of about 10 million samples:
CREATE TABLE Samples (
  sampleID int PRIMARY KEY,
  sampleDate datetime NOT NULL,
  sampleAnalyzed datetime NOT NULL
  -- etc.
)

-- A table of about 44 million results of tests performed on those samples:
CREATE TABLE SampleResults (
  sampleID int NOT NULL REFERENCES Samples,
  testID smallint NOT NULL,
  testValue numeric(9, 3) NOT NULL,
  PRIMARY KEY (sampleID, testID)
)

SELECT setID, COUNT(*) AS sampleCount
FROM (
    SELECT MIN(s1) AS setID, s2
    FROM (
        SELECT R1.sampleID AS s1, R2.sampleID AS s2, COUNT(*) AS ct,
          COUNT(CASE WHEN R1.testID = R2.testID THEN 1 END) AS ct_match
        FROM SampleResults AS R1
        INNER JOIN SampleResults AS R2 ON R1.sampleID <= R2.sampleID
        GROUP BY R1.sampleID, R2.sampleID
      ) AS A
    WHERE ct = ct_match * ct_match
    GROUP BY s2
  ) AS A
GROUP BY setID
ORDER BY setID

CREATE TABLE SampleResults (
  sampleID int NOT NULL,
  testID smallint NOT NULL,
  PRIMARY KEY (sampleID, testID)
)
GO

-- prime the random number generator
DECLARE @dummy float
SET @dummy = RAND(1)

DECLARE @endp float, @sampleID int, @testID smallint
SELECT @endp = 0.5, @sampleID = 1, @testID = 0

SET NOCOUNT ON
BEGIN TRANSACTION

WHILE @sampleID <= 1000000
BEGIN
  SET @testID = @testID + 1 + CAST(FLOOR(RAND()*RAND()*10.0) AS int)

  INSERT INTO SampleResults (sampleID, testID) VALUES (@sampleID, @testID)
  
  IF RAND() < @endp
  BEGIN
    SET @testID = 0
    SET @sampleID = @sampleID + 1
  END
END

COMMIT TRANSACTION
SET NOCOUNT ON
GO

SELECT COUNT(*)
FROM SampleResults

SELECT testsPerSample, COUNT(*) AS sampleCount
FROM (
    SELECT COUNT(*) AS testsPerSample
    FROM SampleResults
    GROUP BY sampleID
  ) AS A
GROUP BY testsPerSample
ORDER BY testsPerSample

SELECT testID, COUNT(*)
FROM SampleResults
GROUP BY testID
ORDER BY testID

SELECT setID, COUNT(*) AS sampleCount
FROM (
    SELECT s2, MIN(s1) AS setID
    FROM (
        SELECT S1.sampleID AS s1, S2.sampleID AS s2
        FROM SampleResults AS S1
        INNER JOIN SampleResults AS S2
          ON S1.sampleID <= S2.sampleID AND S1.testID = S2.testID
        GROUP BY S1.sampleID, S2.sampleID
        HAVING COUNT(*) = (SELECT COUNT(*) FROM SampleResults AS Sa
                           WHERE Sa.sampleID = S1.sampleID)
           AND COUNT(*) = (SELECT COUNT(*) FROM SampleResults AS Sb
                           WHERE Sb.sampleID = S2.sampleID)
      ) AS A
    GROUP BY s2
  ) AS A
GROUP BY setID

SELECT MIN(sampleID) AS setID, COUNT(*)
FROM (
    SELECT sampleID, dbo.UnionBinary(testID) AS testSet
    FROM SampleResults
    GROUP BY sampleID
  ) AS A
GROUP BY testSet
ORDER BY MIN(sampleID)

quote:

---

Originally posted by Arnold Fribble

What I want to do is find all the distinct sets of tests done on a sample, and how many samples have that set.

---

INSERT INTO SampleResults (sampleID, testID)
SELECT 1, 10
UNION ALL SELECT 1, 20
UNION ALL SELECT 1, 30
UNION ALL SELECT 2, 10
UNION ALL SELECT 2, 20
UNION ALL SELECT 2, 30
UNION ALL SELECT 2, 40
UNION ALL SELECT 3, 10
UNION ALL SELECT 3, 30
UNION ALL SELECT 4, 10
UNION ALL SELECT 4, 20
UNION ALL SELECT 4, 30
UNION ALL SELECT 5, 20
UNION ALL SELECT 5, 40
UNION ALL SELECT 6, 40
UNION ALL SELECT 7, 40

setID sampleCount
1     2
2     1
3     1
5     1
6     2

setID tests
1     {10,20,30}
2     {10,20,30,40}
3     {10,30}
5     {20,40}
6     {40}

create function SampleTests(@Sample int)
returns varchar(8000)
as
begin
	declare @ret varchar(8000)
	set @ret = ''
	
	select @ret = @ret + ', ' + convert(varchar(10), TestID) 
        from SampleResults
	where SampleID = @Sample
	order by TestID

	return @ret
end

select Min(SampleID),Count(*), Tests
from
(
select SampleID, dbo.Sampletests(SampleID) as Tests
from
	(select distinct SampleID from Sampleresults) a
) b
group by Tests

SELECT MIN(sampleID) AS setID, COUNT(*) AS sampleCount
FROM (
    SELECT sampleID,
      SUM(CASE WHEN testID BETWEEN 0 AND 62 THEN
        POWER(CAST(2 AS bigint), testID) ELSE 0 END) AS ts1,
      SUM(CASE WHEN testID BETWEEN 63 AND 125 THEN
        POWER(CAST(2 AS bigint), testID - 63) ELSE 0 END) AS ts2
      // extend as necessary
    FROM SampleResults
    GROUP BY sampleID
  ) AS A
GROUP BY ts1, ts2 // extend as necessary
ORDER BY sampleCount DESC, setID ASC