Re: how can you count up the number of columns in a row that have a certain value

Tony

Try this:

SELECT count(*)
FROM bigtable
WHERE DECODE(read1, 0, 1, 0)+DECODE(read2, 0, 1, 0)....+DECODE(read24, 0, 1, 0) > 16;

Allan

tony wrote:

> we have a table with 25 million rows. each row has
>
> acct_# meter_# date read1 read2........read24
>
> 1324 123 01/01/99 14 0 4
>
> we would like a count of the rows that have more than 16 of the 24 read
> columns with a 0 value. we need to exclude then from our analysis.
>
> can somebody post SQL or pl/sql that does this??
>
> also would a bitmap index on the date be of use in speeding up searchs by
> date they go from 7/1/1998 to 12/1/1998, 180 different dates each repeated
> 250,000 times or would a regular one better?
Received on Fri Mar 26 1999 - 07:25:47 CST