count rows into two different variables

3068783

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 ??

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 )

;