Forum Stats

  • 3,759,183 Users
  • 2,251,510 Discussions
  • 7,870,526 Comments

Discussions

count rows into two different variables

3068783
3068783 Member Posts: 4
edited Dec 28, 2015 10:07AM in SQL & PL/SQL

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

Tagged:
KayK

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond
    edited Dec 28, 2015 9:22AM

    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
    Yellow75 Member Posts: 90
    edited Dec 28, 2015 9:33AM

    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
    KayK Member Posts: 1,679 Bronze Crown
    edited Dec 28, 2015 9:29AM

    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
    Lothar Flatz Member Posts: 681 Silver Badge
    edited Dec 28, 2015 10:07AM

    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 )

    ;

    KayK
This discussion has been closed.