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);