tricky plsql question... 2

[eja2000]

i have a table with the following data;


pk MONTH fld2 fld3 fld4
----------------------------------
001 Jan B C D
002 Jan B A E
003 Feb F D E



I want to count the instances of B in any of the fields (fld2 ,fld3,fld4) for a particular month...

for example for the month of Jan ,i should get 2...
thanks..its the plsql i need...

 

[jaggiebunnet]

Try this (I changed some of the data just to show 2 months)

jaggie@DEV>CREATE TABLE T5 (pk NUMBER, MONTH VARCHAR2(10), fld2 VARCHAR2(1), fld3 VARCHAR2(1), fld4 VARCHAR2(1))
  2  /

Table created.

jaggie@DEV>INSERT INTO T5 VALUES (1,'Jan','B','C','D')
  2  /

1 row created.

jaggie@DEV>INSERT INTO T5 VALUES (1,'Jan','B','B','E')
  2  /

1 row created.

jaggie@DEV>INSERT INTO T5 VALUES (1,'Feb','F','D','B')
  2  /

1 row created.

jaggie@DEV>SELECT SUM(cnt), MONTH
  2  FROM
  3  (
  4  SELECT COUNT(*) cnt, MONTH FROM t5 WHERE fld2 = 'B' GROUP BY MONTH
  5  UNION
  6  SELECT COUNT(*) cnt, MONTH FROM t5 WHERE fld3 = 'B' GROUP BY MONTH
  7  UNION
  8  SELECT COUNT(*) cnt, MONTH FROM t5 WHERE fld4 = 'B' GROUP BY MONTH
  9  )
 10  GROUP BY MONTH
 11  /

  SUM(CNT) MONTH
---------- ----------
         1 Feb
         3 Jan

 

[lewisp]

Or

SELECT count(*),
       month
FROM   your_table
WHERE  Instr(fld2 || fld3 || fld4,'B') != 0
GROUP BY month;

 

[jaggiebunnet]

Nice. The only thing is I have tried that on my test table and it doesn't give me back the correct total. I am looking into it to see why that would be. will post any answer I get.

 

[lewisp]

Ah, maybe I see the issue now....

Does this work better?

SELECT Sum(3 - Length(Replace(fld2 || fld3 || fld4,'B',null))),
       month
FROM   t5
GROUP BY month;

 

[eja2000]

lewisp..can you pls explain what your sql below means.i think we are geting close...


Sum(3 - Length(Replace(fld2 || fld3 || fld4,'B',null)))

thx..

 

[eja2000]

how did u arrive at "3" in your statement above lewisp??

 

[jaggiebunnet]

Yep works perfect this time.

Pretty cool way of doing it too!

 

[lewisp]

eja2000, 3 is the number of fields you are concatenating. Taking your example data, [tt]fld2 || fld3 || fld4[/tt] gives you return values of 'BCD','BAE' and 'FDE'. ie, there are 3 characters in each row.

The Replace converts the B to null, leaving 'CD','AE' and 'FDE'.

The length determines how long the resulting string is, in these cases its 2, 2 and 3. If we subtract these from 3 we can determine how many B's are in each string. 1, 1 and 0 in your case.

Finally, we sum the rows and group by the month to give the totals.

 

[eja2000]

thx

 

[zephan]

Why not the basic :

select count(1) hits
from your_table
where month = 'Jan'
and [b] ( [/b] fld1 = 'B' or fld2 = 'B' or fld3 = 'B' [b])[/b]

?

 

[zephan]

Sorry, I see it does'nt count jaggiebunnet's multiple Bs.