Excel Countif in Oracle SQL

Working with data in Excel can be a pain, and sometimes I have to get out of Oracle and use Excel to do certain calculations. I used to use COUNTIF in Excel a lot to sum large columns of data but now I have learnt you can do the same thing in Oracle SQL with the COUNT statement and the CASE statement.

Excel: =COUNTIF(Nx:Ny, >0 )
SQL: COUNT(CASE WHEN col >0 THEN 1 END)

When for instance counting one’s in a binary column in Excel

Excel: =COUNTIF(Nx:Ny, =1)
SQL: COUNT(CASE WHEN col = 1 THEN 1 END)

SELECT COUNT(CASE WHEN col = 1 THEN 1 END) from table

If matching text then put the text in single quotes e.g.

SQL: COUNT(CASE WHEN col = 'test' THEN 1 END)

Countif over multiple columns is done as the sum of one count function per column:

Excel: =COUNTIF(Ax:Cy, >0)
SQL: COUNT(CASE WHEN col_a >0 THEN 1 END) + COUNT(CASE WHEN col_b = 0 THEN 1 END) + COUNT(CASE WHEN col_c = 1 THEN 1 END)

In this SQL I am finding out how many values are greater than 0 and then summing those values together and working out how many percentage rows have a value greater than 0:

select sum(result),(sum(result)/count(*))*100 from (
select COUNT(CASE WHEN > 0 THEN 1 END) as result
from table group by id order by id);