0

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.

Improve this question
2
  • Please check my answer section. I have updated that. I think it will work for you. Please check and let me know. :) Commented Jan 26, 2021 at 8:36
  • Is the purpose to avoid listing out the columns? If so, the question should be clear. Commented Jan 26, 2021 at 12:53

3 Answers 3

Reset to default
2

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

select count(*),
       (select count(*) from (select distinct * from table))
from table;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

It still don't give both counts and distinct count, as select distinct * from table inner query filter only distinct rows. Right?
@AliHasan no, the filter only applies to the subquery, not the outer query. The outer query still selects all rows from table.
0 

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
Improve this answer

5 Comments

While this code may answer the question, it is more useful to OP and future readers if you explain a bit more why and how this code solved the problem
But it is taking count of one column in inner query. What if we have 15+ columns in a table?
In the sub query you group by each column in your table in order to get all groups of identical rows, then the outer select statements gets you the number of such kind of groups, which is the distinct count, and sum of the counts of eanch group is the total count
putting all the columns in group by is good idea? when you have 15+ columns
actually the distinct operator is one of the heavy operators and you can't avoid it in your query, so I guess it will have approximately the same efficiency as groupping by the all columns. The same thing we can say for partition by operator. You can enable query analyzer and see what is going on, but in my opinion they will be approximately the same.
0

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.

Improve this answer

1 Comment

No I don't need window function. I simply want to get count and distinct count of rows.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.