Home » SQL & PL/SQL » SQL & PL/SQL » URGENT - display count for criteria....
URGENT - display count for criteria.... [message #209496] Fri, 15 December 2006 03:21 Go to next message
svik
Messages: 16
Registered: April 2005
Location: MRU
Junior Member
Hi to all,

I have a table consisting of 12 columns of data type number. for each row i want to display a count wherever the value of a row is greater than 100, that is, how many times the value greater than 100 appear in the 12 columns.

For example:

Col1 Col2 Col3 Col5 Col6 Col7... Col12 Count
24 76 101 45 100 43 99 1
34 123 100 12 4 46 88 0
78 145 101 3 123 12 89 3

PLease help???

Thanks

[Updated on: Fri, 15 December 2006 03:26]

Report message to a moderator

Re: URGENT - display count for criteria.... [message #209499 is a reply to message #209496] Fri, 15 December 2006 03:55 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
The SIGN function in combination with DECODE can help you out:

My script:
WITH yourtable as ( SELECT 100 n1, 200 n2 FROM dual UNION ALL -- 1
                    SELECT  99 n1, 100 n2 FROM dual UNION ALL -- 0
                    SELECT 101 n1,  88 n2 FROM dual UNION ALL -- 1
                    SELECT  97 n1, 101 n2 FROM dual UNION ALL -- 1
                    SELECT 102 n1,  98 n2 FROM dual UNION ALL -- 1
                    SELECT 103 n1, 101 n2 FROM dual UNION ALL -- 2
                    SELECT  88 n1, 520 n2 FROM dual UNION ALL -- 1
                    SELECT 104 n1,  66 n2 FROM dual UNION ALL -- 1
                    SELECT  87 n1,  33 n2 FROM dual UNION ALL -- 0
                    SELECT 120 n1,  65 n2 FROM dual UNION ALL -- 1
                    SELECT 205 n1, 562 n2 FROM dual UNION ALL -- 2
                    SELECT  44 n1, 214 n2 FROM dual UNION ALL -- 0
                    SELECT  33 n1,  33 n2 FROM dual 
                  ) 
SELECT n1
     , n2
     , DECODE(SIGN(n1-100),1,1,0)+
       DECODE(SIGN(n2-100),1,1,0) cnt
FROM   yourtable
/


My run:
SQL> @orafaq

        N1         N2        CNT
---------- ---------- ----------
       100        200          1
        99        100          0
       101         88          1
        97        101          1
       102         98          1
       103        101          2
        88        520          1
       104         66          1
        87         33          0
       120         65          1
       205        562          2

        N1         N2        CNT
---------- ---------- ----------
        44        214          1
        33         33          0

13 rows selected.


[edit]Misread another post Confused

MHE

[Updated on: Fri, 15 December 2006 06:41]

Report message to a moderator

Previous Topic: LEFT JOIN
Next Topic: execute immediate error
Goto Forum:
  


Current Time: Sat Dec 03 07:48:33 CST 2016

Total time taken to generate the page: 0.13901 seconds