Count on Table 1 based on Count with Clause on Table 2, sql-CodePudding

Table 1

Table 2

I need to find the Count of total number of unique stores that have "Achieved Date" not Null that achieved all of the "Achievement Ids" "enabled" on Table 2.

So far I can find the count of stores that achieved a hard coded number, but I'm not breaking through the part where I use the Count of Enabled Ids on table 2 to define what the number is.

SELECT 
    COUNT(*) AS count 
FROM 
    (SELECT 
         StoreNumber, COUNT(*) as Achievements 
     FROM 
         StoreAchievementProgress
     WHERE 
         AchievedDate IS NOT NULL 
     GROUP BY 
         StoreNumber) count

CodePudding user response：

maybe this query

SELECT S.StoreNumber
FROM StoreAchievementProgress S 
RIGHT JOIN (SELECT Id FROM Table2 WHERE Enabled=1 )T
    ON T.Id=S.AchievementId
    AND AchievedDate IS NOT NULL

GROUP BY S.StoreNumber  
HAVING COUNT(1) = (SELECT COUNT(Id) FROM Table2 WHERE Enabled=1 )

CodePudding user response：

Joining the stores with a count of their enabled achievements to how many they can get

SELECT COUNT(*) AS StoresFullAchievements
FROM 
(
  SELECT p.StoreNumber, COUNT(*) AS TotalEnabledAchievements
  FROM StoreAchievementProgress p
  JOIN Achievements a ON a.id = p.AchievementId
  WHERE p.AchievedDate IS NOT NULL
    AND a.Enabled = 1
  GROUP BY p.StoreNumber
) AS s
JOIN 
(
  SELECT COUNT(*) AS TotalEnabled
  FROM Achievements 
  WHERE Enabled = 1
) a 
ON a.TotalEnabled = s.TotalEnabledAchievements