How can I run this query for each id in a table

Go To StackoverFlow.com

2

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?

2012-04-03 20:33
by dido
IF site_id is pk than it will be always 1 - levi 2012-04-11 11:06


3

try

SELECT count(*),site_id FROM site WHERE category = 'S' AND active = 'Y'  
GROUP BY site_id
2012-04-03 20:34
by SQLMenace