Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how can you count up the number of columns in a row that have a certain value

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

From: Arlette BROSSARD <abray_at_club-internet.fr>
Date: 28 Mar 1999 22:06:28 GMT
Message-ID: <01be7967$299e86c0$b8a524c3@ntdv2113>


You can try this, but i tested it with less than 25 million rows, it's very fast with 25 lines !

SELECT COUNT(*)
  FROM tablex

 WHERE DECODE(read01,0,1,0)

+ DECODE(read02,0,1,0)
+ DECODE(read03,0,1,0)
+ DECODE(read04,0,1,0)
+ DECODE(read05,0,1,0)
+ DECODE(read06,0,1,0)
+ DECODE(read07,0,1,0)
+ DECODE(read08,0,1,0)
+ DECODE(read09,0,1,0)
+ DECODE(read10,0,1,0)
+ DECODE(read11,0,1,0)
+ DECODE(read12,0,1,0)
+ DECODE(read13,0,1,0)
+ DECODE(read14,0,1,0)
+ DECODE(read15,0,1,0)
+ DECODE(read16,0,1,0)
+ DECODE(read17,0,1,0)
+ DECODE(read18,0,1,0)
+ DECODE(read19,0,1,0)
+ DECODE(read20,0,1,0)
+ DECODE(read21,0,1,0)
+ DECODE(read22,0,1,0)
+ DECODE(read23,0,1,0)
+ DECODE(read24,0,1,0) > 16;
tony <ramdan_at_mailexcite.com> a écrit dans l'article <rBAK2.11164$gn1.40831_at_newscene.newscene.com>... > > 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 Sun Mar 28 1999 - 16:06:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US