CODEWITHSUNDEEP
sql
Pablo Aguirre de Souza
Pablo Aguirre de Souza

Reputation: 4149

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

Table 1

enter image description here

Table 2

enter image description here

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

Upvotes: 1

Views: 38

Answers (2)

LukStorms
LukStorms

Reputation: 29667

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

Upvotes: 1

DhruvJoshi
DhruvJoshi

Reputation: 17126

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 )

Upvotes: 1

Related Questions