Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

count rows into two different variables

3068783Dec 28 2015 — edited Dec 28 2015

Hi.

Is there any possible to count rows with condition ?

Generally I want to have something like this:

if field like 'some%' then count1 else count2.

Is there any possibility ??

Comments

Frank Kulash

Hi,

3068783 wrote:

Hi.

Is there any possible to count rows with condition ?

Generally I want to have something like this:

if field like 'some%' then count1 else count2.

Is there any possibility ??

You can use an expression such as

CASE

     WHEN  field  LIKE 'some%'

     THEN  'Like some%'

     ELSE  'Unlike some%'

END

Here's an example using the scott.emp table, that counts how many employees have jobs starting with 'SA', and how many don't:

WITH    got_grp    AS

(

    SELECT  CASE

                WHEN  job  LIKE  'SA%'

                THEN  'Like SA%'

                ELSE  'Unlike SA%'

            END     AS grp

    FROM    scott.emp

--  WHERE   ...     -- If needed

)

SELECT    grp

,         COUNT (*)   AS num_found

FROM      got_grp

GROUP BY  grp

ORDER BY  grp

;

Output:

GRP        NUM_FOUND

---------- ---------

Like SA%           4

Unlike SA%        10

Yellow75

Something like this? (but not very efficient...):

SELECT

CASE WHEN FIELD1=cond1 THEN (SELECT COUNT(*) FROM MS038_METERS T2 WHERE T1.FIELD1=T2.FIELD1) AS COUNT_ALL_cond1

ELSE 0 END,

CASE WHEN FIELD1=cond2 THEN  (SELECT COUNT(*) FROM MS038_METERS T2 WHERE T1.FIELD1=T2.FIELD1) AS COUNT_ALL_cond2

ELSE 0 END

--etc

FROM TABLE1 T1

GROUP BY

FIELD1;

You have the full result on each row.

KayK

Hi,

perhaps you're looking for something like this:

--

select sum( case when owner     like 'SYS%' then 1 else 0 end ) count_sys,

       sum( case when owner not like 'SYS%' then 1 else 0 end ) count_others

  from (select * from dba_tables where rownum < 1000 ); 

--

regards

Kay

Lothar Flatz

I prefer using NULL values instead of 0. From the result it is the same, but the null value is ignored (skipped ) by the group function, thus we could use count which is clearer from the meaning:

select COUNT( case when owner     like 'SYS%' then 1 else NULL end ) count_sys,   

          COUNT( case when owner not like 'SYS%' then 1 else NULL end ) count_others 

    from (select * from dba_tables where rownum < 1000 )

;

1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 25 2016
Added on Dec 28 2015
4 comments
367 views