Clash Royale CLAN TAG

How to count unique rows in Oracle

I have an oracle database table with a lot of columns. I'd like to count the number of fully unique rows. The only thing I could find is:

SELECT COUNT(DISTINCT col_name) FROM table;


This however would require me listing all the columns and I haven't been able to come up with syntax that will do that for me. I'm guessing the reason for that is that this query would be very low performance? Is there a recommended way of doing this?

5 Answers
5

How about

SELECT COUNT(*)
FROM (SELECT DISTINCT * FROM Table)


I think you want a count of all distinct rows from a table like this

select count(1) as c
from (
select distinct *
from tbl
) distinct_tbl;


It depends on what you are trying to accomplish.
To get a count of the distinct rows by specific column, so that you know what data exists, and how many of that distinct data there are:

SELECT DISTINCT

A_CODE, COUNT(*)

FROM MY_ARCHV

GROUP BY A_CODE


--This informs me there are 93 unique codes, and how many of each of those codes there are.

Another method

--How to count how many of a type value exists in an oracle table:
select A_CDE, --the value you need to count
count(*) as numInstances --how many of each value
from A_ARCH -- the table where it resides
group by A_CDE -- sorting method

select A_CDE

count(*) as numInstances

from A_ARCH

group by A_CDE

Either way, you get something that looks like this:

A_CODE Count(*)

1603 32

1600 2

1605 14


SELECT DISTINCT col_name, count(*) FROM table_name group by col_name

with fn1 as
(
select distinct
from
)
select count(*) from fn1

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.