How to create subquery with distinct in Oracle SQL

Question

I have the following two ORACLE SQL queries which work well:

SELECT COUNT(SEG_ID) INTO totalUniqueSegments FROM CAR_RENTAL_SERVICES;

SELECT DISTINCT SEG_ID FROM CAR_RENTAL_SERVICES;

But I need to combine them. I'd like my count to consider only unique seg_id.

This is what I've tried:

SELECT COUNT(SEG_ID)INTO totalUniqueSegments FROM CAR_RENTAL_SERVICES WHERE SEG_ID IN (SELECT DISTINCT SEG_ID FROM CAR_RENTAL_SERVICES);

But I'm getting the error 'missing expression'. It should be fairly simple but I'm not very experienced. Thanks.

EDIT: this COUNT(SEG_ID)INTO was changed to this COUNT(SEG_ID) INTO (with space). Now the error is 'missing expression'

Maybe you're missing a column name for COUNT(SEG_ID) since you're putting it into another table. — glaeran
– glaeran, Commented Apr 15, 2014 at 18:17

Dba · Accepted Answer · 2014-04-15 18:55:56Z

4

Try like this,

SELECT COUNT(DISTINCT SEG_ID) FROM CAR_RENTAL_SERVICES;

Use the below query, If You want to demonstrate the same using a sub-query.

SELECT COUNT(*)
FROM(
     SELECT DISTINCT seg_id 
     FROM   car_rental_services
     );

edited Apr 15, 2014 at 18:55

answered Apr 15, 2014 at 18:16

Dba

6,6791 gold badge26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

PrincessLilly Over a year ago

Your solution is great and it works. However I needed to demonstrate using a sub-query for a school exercise. Is it possible in this case or should I abandon the idea ? Thanks ! (I can mark your solution in any case)

Dba Over a year ago

@PrincessLilly, Please find the updated answer with sub-query.

Collectives™ on Stack Overflow

How to create subquery with distinct in Oracle SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related