Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 234 Big Data Appliance
- 1.9K Data Science
- 449.7K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.2K SQL Developer
- 295.3K Development
- 17 Developer Projects
- 138 Programming Languages
- 292K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 27 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 157 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 387 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1K Español
- 1.9K Japanese
- 230 Portuguese
select single count in isolation

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
Best Answers
-
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.
-
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.
-
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 ;
Answers
-
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.
-
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.
-
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
-
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.
-
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');
-
@Billy Verreynne your query actually provided the result I am expecting. Is there another method you could provide please for 9i.
-
select * from (select t.*, count(nullif(t.charge_type,'RCs')) over(partition by t.service_id) c from table1 t) where c = 0;
-
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.
-
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.
-
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.