I have the following query that I'd like to run for each site_id in a table. the site_id is also a primary key.

SELECT count(*)
FROM site
WHERE category = 'S'
    AND active = 'Y'
    AND site_id = -- This is what i'm trying to dynamically input 

Then I want to take that count(*) value and insert it into a different table...for all of the ids. Is there any way to do this using a set based solution? Or do I have to somehow iterate through all the site_id's and input that into the query?

• sql
• database
• select
• set

try

SELECT count(*),site_id FROM site WHERE category = 'S' AND active = 'Y'  
GROUP BY site_id