0

My task is to analyze a big table (250 columns, millions of rows). I need to find out, how many defined values (e.g. -1) there are in each column. I have a solution that loops through the columns of my table and uses the methods described in the following links:

Fastest way to count exact number of rows in a very large table?

https://learn.microsoft.com/de-de/archive/blogs/martijnh/sql-serverhow-to-quickly-retrieve-accurate-row-count-for-table

However, I have to do:

select column into #tab from MyBigTable where column = -1

And then apply the methods to #tab.

Do you see any way how this can be efficiently dealt with?

Boris
  • 79
  • 5

1 Answers1

0

You could conditionally aggregate

select sum(case when col1  = -1 then 1 else 0 end) col1sum,
       sum(case when col2  = -1 then 1 else 0 end) col2sum,
       ...
       ...
       sum(case when coln  = -1 then 1 else 0 end) colnsum
from yourtable
P.Salmon
  • 17,104
  • 2
  • 12
  • 19