select single count in isolation

ricard888

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

Billy Verreynne

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

https://community.oracle.com/tech/developers/discussion/comment/16802898#Comment_16802898

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

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
;

Billy Verreynne

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

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

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

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

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

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

User_H3J7U

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

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

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

https://community.oracle.com/tech/developers/discussion/comment/16802898#Comment_16802898

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.