Forum Stats

  • 3,770,126 Users
  • 2,253,070 Discussions
  • 7,875,331 Comments

Discussions

select single count in isolation

ricard888
ricard888 Member Posts: 180 Bronze Badge
edited Jul 20, 2021 8:14AM in SQL & PL/SQL

Hi all, I want to select a service_id whose charge type = 'RCs' in isolation. So if their other charge types for the same service_id I don't want it. Please see the sample data and the result I want. I am using a very old Oracle 9i. Many thanks

CREATE TABLE table1
(
 SERVICE_ID VARCHAR2(50) 
, BAN VARCHAR2(20) NOT NULL 
, DOMAIN VARCHAR2(30) 
, CHARGE_TYPE VARCHAR2(30) NOT NULL 
)

Insert into table1 (SERVICE_ID,BAN,DOMAIN,CHARGE_TYPE) values ('0XXX104413','20XXX75438158','Mobile','DDP');
Insert into table1 (SERVICE_ID,BAN,DOMAIN,CHARGE_TYPE) values ('0XXX104413','20XXX75438158','Mobile','RCs');
Insert into table1 (SERVICE_ID,BAN,DOMAIN,CHARGE_TYPE) values ('0BBB112529','20XXX45118773','Mobile','RCs');
Insert into table1 (SERVICE_ID,BAN,DOMAIN,CHARGE_TYPE) values ('0ZZZ375066','20XXX15941881','Mobile','Main Plan');
Insert into table1 (SERVICE_ID,BAN,DOMAIN,CHARGE_TYPE) values ('0ZZZ375066','20XXX15941881','Mobile','RCs');

This what I am expecting in the result. Many thanks

"SERVICE_ID","BAN","DOMAIN","CHARGE_TYPE"
"0BBB112529","20XXX45118773","Mobile","RCs"

SORRY edited the record


Tagged:

Best Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,596 Red Diamond
    Accepted Answer

    Another method:

    SQL> select 
     2       * 
     3 from  table1 
     4 where charge_type = 'RCs' 
     5 and   service_id in( 
     6         select service_id from table1 
     7         group by service_id 
     8         having count(*) = 1 
     9 ); 
     
    SERVICE_ID          BAN                 DOMAIN    CHARGE_TYP 
    -------------------- -------------------- ---------- ---------- 
    0BBB112529          20XXX45118773       Mobile    RCs 
     
    1 row selected.
    

    Note that this does not ensure a single row result.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    Accepted Answer

    So what results do you expect back?

    select  service_id,
            max(ban) ban,
            max(domain) domain,
            'RCs' charge_type
      from  table1
      where charge_type = 'RCs'
      group by service_id
      having count(*) = 1
    /
    
    SERVICE_ID BAN                  DOMAIN CHARGE_TYPE
    ---------- -------------------- ------ -----------
    0XXX104413 20XXX75438158        Mobile RCs
    0ZZZ375066 20XXX15941881        Mobile RCs
    0WWW112529 20WWW4511999         Mobile RCs
    
    SQL>
    
    

    Or:

    select  service_id,
            max(ban) ban,
            max(domain) domain,
            'RCs' charge_type
      from  table1
      group by service_id
      having count(*) = 1
         and max(charge_type) = 'RCs'
    /
    
    SERVICE_ID BAN                  DOMAIN CHARGE_TYPE
    ---------- -------------------- ------ -----------
    0WWW112529 20WWW4511999         Mobile RCs
    
    
    SQL>
    

    SY.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond
    Accepted Answer

    Hi, @ricard888

    Yet another way:

    SELECT    rcs.*
    FROM	  table1 rcs
    LEFT JOIN table1 oth  ON  oth.service_id = rcs.service_id
       	  	      AND oth.ROWID      <> rcs.ROWID
    WHERE     rcs.charge_type = 'RCs'
    AND	  oth.charge_type IS NULL
    ORDER BY  rcs.service_id -- or whatever you want
    ;
    


«1

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,596 Red Diamond

    Nonsensical requirement statement.

    Why that specific RC charge type row? Why not another RC type row?

    You have failed to provide a comprehensive requirement statement.

    Try again.

  • ricard888
    ricard888 Member Posts: 180 Bronze Badge

    if there are other charge type other than RCs for the same service_id then I don't have want it that is my requirement.

  • cormaco
    cormaco Member Posts: 1,723 Bronze Crown

    I think this is what you want:

    select * from table1 t1
    where CHARGE_TYPE = 'RCs' and not exists (
        select 1 from table1
        where
            SERVICE_ID = t1.SERVICE_ID and
            CHARGE_TYPE <> 'RCs'
    );
    
    SERVICE_ID      BAN                  DOMAIN                         CHARGE_TYPE    
    --------------- -------------------- ------------------------------ ---------------
    0BBB112529      20XXX45118773        Mobile                         RCs            
    
    
    
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,596 Red Diamond
    Accepted Answer

    Another method:

    SQL> select 
     2       * 
     3 from  table1 
     4 where charge_type = 'RCs' 
     5 and   service_id in( 
     6         select service_id from table1 
     7         group by service_id 
     8         having count(*) = 1 
     9 ); 
     
    SERVICE_ID          BAN                 DOMAIN    CHARGE_TYP 
    -------------------- -------------------- ---------- ---------- 
    0BBB112529          20XXX45118773       Mobile    RCs 
     
    1 row selected.
    

    Note that this does not ensure a single row result.

  • ricard888
    ricard888 Member Posts: 180 Bronze Badge

    Sorry fellows. I have changed the data sample to include a duplicate RCs charge type to service id 0BBB112529 and added an additional single row for service id 0WWW112529 with RCs charge. Below is my new sample data.

    Insert into table1 (SERVICE_ID,BAN,DOMAIN,CHARGE_TYPE) values ('0XXX104413','20XXX75438158','Mobile','DDP');
    Insert into table1 (SERVICE_ID,BAN,DOMAIN,CHARGE_TYPE) values ('0XXX104413','20XXX75438158','Mobile','RCs');
    Insert into table1 (SERVICE_ID,BAN,DOMAIN,CHARGE_TYPE) values ('0BBB112529','20XXX45118773','Mobile','RCs');
    Insert into table1 (SERVICE_ID,BAN,DOMAIN,CHARGE_TYPE) values ('0BBB112529','20XXX4511999','Mobile','RCs');
    Insert into table1 (SERVICE_ID,BAN,DOMAIN,CHARGE_TYPE) values ('0WWW112529','20WWW4511999','Mobile','RCs');
    Insert into table1 (SERVICE_ID,BAN,DOMAIN,CHARGE_TYPE) values ('0ZZZ375066','20XXX15941881','Mobile','Main Plan');
    Insert into table1 (SERVICE_ID,BAN,DOMAIN,CHARGE_TYPE) values ('0ZZZ375066','20XXX15941881','Mobile','RCs');
    
  • ricard888
    ricard888 Member Posts: 180 Bronze Badge

    @Billy Verreynne your query actually provided the result I am expecting. Is there another method you could provide please for 9i.

  • User_H3J7U
    User_H3J7U Member Posts: 690 Silver Trophy
    select *
    from (select t.*, count(nullif(t.charge_type,'RCs')) over(partition by t.service_id) c from table1 t)
    where c = 0;
    
  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,596 Red Diamond

    Is there another method you could provide please for 9i.

    Do not have a 9i database anymore - and unsure if and what syntax 9i supports for CTE and analytical functions and so on.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,233 Red Diamond

    Hi, @ricard888

    I have changed the data sample ...

    Don't forget to post the desired results given the changed data.

    your query actually provided the result I am expecting. Is there another method you could provide please for 9i.

    Why? If the solution Billy posted is doing what you want, why do you want another method? Whatever you don't like about it is liable to be just as bad in another solution.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,925 Red Diamond
    Accepted Answer

    So what results do you expect back?

    select  service_id,
            max(ban) ban,
            max(domain) domain,
            'RCs' charge_type
      from  table1
      where charge_type = 'RCs'
      group by service_id
      having count(*) = 1
    /
    
    SERVICE_ID BAN                  DOMAIN CHARGE_TYPE
    ---------- -------------------- ------ -----------
    0XXX104413 20XXX75438158        Mobile RCs
    0ZZZ375066 20XXX15941881        Mobile RCs
    0WWW112529 20WWW4511999         Mobile RCs
    
    SQL>
    
    

    Or:

    select  service_id,
            max(ban) ban,
            max(domain) domain,
            'RCs' charge_type
      from  table1
      group by service_id
      having count(*) = 1
         and max(charge_type) = 'RCs'
    /
    
    SERVICE_ID BAN                  DOMAIN CHARGE_TYPE
    ---------- -------------------- ------ -----------
    0WWW112529 20WWW4511999         Mobile RCs
    
    
    SQL>
    

    SY.