CODEWITHSUNDEEP
sqlapache-sparkcountapache-spark-sqldistinct
Ali Hasan
Ali Hasan

Reputation: 602

best way to get count and distinct count of rows in single query

What is the best way to get count of rows and distinct rows in a single query?

To get distinct count we can use subquery like this:

select count(*) from
(
   select distinct * from table
)

I have 15+ columns and have many duplicates rows as well and I want to calculate count of rows as well as distinct count of rows in one query.

More if I use this

select  count(*) as Rowcount , count(distinct *) as DistinctCount from table

This will not give accurate results as count(distinct *) doesn't work.

Upvotes: 0

Views: 1192

Answers (3)

Srijon Chakraborty
Srijon Chakraborty

Reputation: 2164

I think I have understood what you are looking for. You need to use some window function.
So, you query should be look like =>

Select  COUNT(*) OVER() YourRowcount , 
        COUNT(*) OVER(Partition BY YourColumnofGroup) YourDistinctCount --Basic of the distinct count
FROM Yourtable

NEW Update

select top 1 
       COUNT(*) OVER() YourRowcount, 
       DENSE_RANK()  OVER(ORDER BY YourColumn) YourDistinctCount 
FROM Yourtable ORDER BY TT DESC

Note: This code is written sql server. Please check the code and let me know.

Upvotes: 0

mck
mck

Reputation: 42422

Why don't you just put the subquery inside another query?

select count(*),
       (select count(*) from (select distinct * from table))
from table;

Upvotes: 2

Karen Avdalyan
Karen Avdalyan

Reputation: 392


create table tbl
(
col int
);

insert into tbl values(1),(2),(1),(3);

select count(*) as distinct_count, sum(sum) as all_count 
from (
select count(col) sum from tbl group by col
)A

Upvotes: 0

Related Questions