CODEWITHSUNDEEP
sqlcount
45th Percentile
45th Percentile

Reputation: 53

SQL - count the number of occurrences in all columns

I am trying to create some validation routines for an analytics system.

We have large tables with data. If a particular calculation all or most of a column might be zero. In practice values aren't usually exactly 0, so many occurrences of 0 indicates a potential issue. I want to write a single query that counts the number of 0's in each columnn. For instance, if my data was:

col1 col2 col3 col4 col5
1 0 23 2 9
2 0 54 3 -2
3 3 45 4 3
4 0 32 0 1
5 0 0 0 9

I would like to get a count of the number of occurrences of 0 in each column. The result for the table above would be: (0,4,1,2,0)

Is there a good way to do this in SQL for tables with an arbitrary number of columns?

Obviously I can't do:

select count(col1), count(col2), count(col3), count(col4), count(col5)
from table 
where col1 = 0 and col2 = 0 and col3 = 0 and col4 = 0 and col5=0

Upvotes: 1

Views: 151

Answers (3)

Azbilegt Chuluunbat
Azbilegt Chuluunbat

Reputation: 11

SELECT SUM(case when col1 = 0 then 1 else 0 end) col1_0,
SUM(case when col2 = 0 then 1 else 0 end) col2_0,
SUM(case when col3 = 0 then 1 else 0 end) col3_0,
SUM(case when col4 = 0 then 1 else 0 end) col4_0
FROM yourTable;

Upvotes: 0

Popeye
Popeye

Reputation: 35900

I would suggest using WHERE condition and conditional aggregation to filter the row before aggregation as follows:

SELECT COUNT(CASE WHEN col1 = 0 THEN 1 END) AS col1_0_count,
       COUNT(CASE WHEN col2 = 0 THEN 1 END) AS col2_0_count,
       COUNT(CASE WHEN col3 = 0 THEN 1 END) AS col3_0_count,
       COUNT(CASE WHEN col4 = 0 THEN 1 END) AS col4_0_count,
       COUNT(CASE WHEN col5 = 0 THEN 1 END) AS col5_0_count
  FROM yourTable t
 WHERE 0 IN (col1, col2, col3, col4, col5);

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521093

Use conditional aggregation here:

SELECT
    COUNT(CASE WHEN col1 = 0 THEN 1 END) AS cnt_col1,
    COUNT(CASE WHEN col2 = 0 THEN 1 END) AS cnt_col2,
    COUNT(CASE WHEN col3 = 0 THEN 1 END) AS cnt_col3,
    COUNT(CASE WHEN col4 = 0 THEN 1 END) AS cnt_col4,
    COUNT(CASE WHEN col5 = 0 THEN 1 END) AS cnt_col5
FROM yourTable;

Note that on databases which support summing boolean expressions, the above might be simplified to:

SELECT
    SUM(col1 = 0) AS cnt_col1,
    SUM(col2 = 0) AS cnt_col2,
    SUM(col3 = 0) AS cnt_col3,
    SUM(col4 = 0) AS cnt_col4,
    SUM(col5 = 0) AS cnt_col5
FROM yourTable;

Upvotes: 2

Related Questions