0

I need to run a SQL statement that gets a count and has a specific ID value in the where clause. The SQL itself isnt an issue, the issue is I need to rerun this script multiple times using a different ID in the where clause. I could run the script manually and just update the ID every time but I was wondering if there was a way to declare an array or list or something and store the list of IDs in there. Then I'd iterate through the array and replace the ID with the next item in the array each time and then print the count result to the screen for each ID.

For example if I was going to write something in C# to do this I would do something like this:

List<int> newlist = List<int>(){1,2,3,4,5}
foreach(var item in newlist)
{
    //excuse syntax this is just an example
    sql = "SELECT count(*) FROM testtable WHERE ID = " + item
}

Any help is greatly appreciated!

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Kristen
  • 443
  • 1
  • 12
  • 25
  • Possible duplicate of [How can I select from list of values in SQL Server](https://stackoverflow.com/questions/1564956/how-can-i-select-from-list-of-values-in-sql-server) – Harsha Sep 10 '19 at 18:09
  • 2
    I think what you are looking for is grouping by ID and counting items in each group + select items where id is in array. https://www.dofactory.com/sql/group-by – Nick Surmanidze Sep 10 '19 at 18:09
  • Why don't you do something like: `SELECT count(*) FROM testtable WHERE ID in (1, 2, 3, 4, 5)`? – The Impaler Sep 10 '19 at 18:12
  • @TheImpaler I was just thinking I wanted to run each statement separately to get a separate count for each ID. I dont want a total count – Kristen Sep 10 '19 at 18:20
  • You can achieve that using `temp table` or a `string` to store values, `Cursor or while` to do looping. Even I suggest to do group by `id` with count. Let me know what are exact requirements, why you want to do it. So we can proceed further. – Dark Knight Sep 10 '19 at 18:29
  • SQL is a set based language. Why would you want a loop? – Eric Sep 10 '19 at 18:32
  • 1
    SELECT id,count(*) AS cnt FROM testtable WHERE ID IN ( YoursIds) GROUP BY id; – PeterHe Sep 10 '19 at 19:04

0 Answers0