-- Seriously how many of you opened all four of these?
DROP TABLE IF EXISTS #SumOf96_32Cap
SELECT DISTINCT
d1 + d2 + d3 + d4 + d5 + d6 + d7 + d8 AS Total
, CASE WHEN d1 < 3 THEN 1 ELSE 0 END + CASE WHEN d2 < 3 THEN 1 ELSE 0 END + CASE WHEN d3 < 3 THEN 1 ELSE 0 END + CASE WHEN d4 < 3 THEN 1 ELSE 0 END + CASE WHEN d5 < 3 THEN 1 ELSE 0 END + CASE WHEN d6 < 3 THEN 1 ELSE 0 END + CASE WHEN d7 < 3 THEN 1 ELSE 0 END + CASE WHEN d8 < 3 THEN 1 ELSE 0 END AS ShortPlayerCount
, d1, d2, d3, d4, d5, d6, d7, d8
--, *
INTO #SumOf96_32Cap
from
(VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48)) d1 (d1)
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48)) d2 (d2)
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48)) d3 (d3)
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48)) d4 (d4)
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48)) d5 (d5)
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48)) d6 (d6)
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48)) d7 (d7)
CROSS APPLY (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48)) d8 (d8)
WHERE
d1 + d2 + d3 + d4 + d5 + d6 + d7 + d8 = 96
AND d1 >= d2 AND d2 >= d3 AND d3 >= d4 AND d4 >= d5 AND d5 >= d6 AND d6 >= d7 AND d7 >= d8
declare @Total AS INT = (SELECT COUNT(*) FROM #SumOf96_32Cap) -- 587819
SELECT ShortPlayerCount,
COUNT(*) AS ShortCombinations,
(COUNT(*) * 1.0 / @Total) * 100 AS ShortPercentage
FROM #SumOf96_32Cap
GROUP BY ShortPlayerCount
ORDER BY ShortPlayerCount