Any Oracle SQL Fundis?

[Bernie]

I need to do the following:

Say I have a table with 1 column with the following data:

Col1
----
b
a
c
a
d
a
e
f
d
c


I want to be able to count how many unique entries there are for only a,b & c

ie, the answer I want is 3, as there is at least 1 a, 1 b and 1 c.

Its pretty trivial just looking at it but I am after a SQL command to do it.

if I do the following:

select count(*)
from table
where col1 in ('a','b','c')

I end up with 6 as it double counts the duplicates.


Thanks

B.

 

[TiredOfWaiting]

Not the most elegant solutions, but the best I can come up with...

WITH GROUPQUERY AS
(
SELECT COL1 FROM TABLE GROUP BY COL1
)
SELECT COUNT(*) FROM GROUPQUERY

or

WITH UNIQUEQUERY AS
(
SELECT UNIQUE COL1 FROM TABLE
)
SELECT COUNT(*) FROM UNIQUEQUERY

The second is is definitely better according to me, but the first allows you to group other columns as well.

 

[eye_suc]

count distinct ?

SELECT COUNT(DISTINCT(column1))
FROM testtable
WHERE column1 IN ('a', 'b', 'c')


so your query is mostly right, just omitted the distinct

 

[JayT]

select count(distinct col1) from table
where col1 in ('a','b','c');

 

[JayT]

I checked the above code in mysql. It works

 

[Bernie]

I checked the above code in mysql. It works

Ahhh Excellent, I knew it was something simple. Thanks for all you help and replies

Cheers
B..