CODEWITHSUNDEEP
sqloracle-databasecount
Omri
Omri

Reputation: 1646

Count how many rows have at least one certain value

I'm using Oracle SQL and i need a help with a query.

I need to count how many rows have at least one value of NA.

My table have more than 300 columns (these are not the real columns names):

col1 varchar,
col2 varchar,
col3 varchar,
...
...
...
col300 varchar

Here is an example of a table:

col1 | col2 | col3 | col4
 10  | 15   | 55   |  NA
 NA  | 15   | 55   |  NA
 10  | 15   | 55   |  33
 10  | NA   | 55   |  58
 10  | 15   | 55   |  33
 10  | 15   | 55   |  84
 10  | 15   | NA   |  40

Output expected result: 4

Please advise.

Upvotes: 0

Views: 313

Answers (3)

Omri
Omri

Reputation: 1646

Well, I've found the solution.

select sum(cnt) from
(
select case when 
       col1 = 'NA'
    or col2 = 'NA'
    or col3 = 'NA'
    or col4 = 'NA' then 1 end cnt
from MyTable  
)

Upvotes: 0

Okdel
Okdel

Reputation: 183

Use count

SELECT
  COUNT(1)
FROM
  table_name
WHERE
  'NA' IN (col1, col2, ..., col300)

Upvotes: 0

Christian Barron
Christian Barron

Reputation: 2755

You can do a Sum of a sub select using a case to get this:

Select SUM(NA)

From

(Select case when col1 = 'NA' then 1 case when col2 = 'NA' then 1 case when col3 = 'NA' then 1 case when col4 = 'NA' then 1 end as NA
from Table) a

Upvotes: 1

Related Questions