How to get the total number of records without duplicating

Asked By jennina yale 20 points N/A Posted on - 03/08/2012

I want to get the total number of records retained without DUPLICATE ones in my database. IS this correct?

 

 

 

 

• Status: Open
• Question Views: 839
• Answer Count: 3
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS SQL Server

Best Answer by Sharath Reddy

Go To Solution

Answered By Miniaoh Pultu 0 points N/A #90545

How to get the total number of records without duplicating

 

Hello Jennina,

That is possible, but you will have to do some extra work JFirst you’ll have to count all records in a  column using this formula:

SELECT COUNT (jennina’s column) FROM (jennina’s_table name)

After that you have to count duplicate rows with this formula

SELECT (jennina’s column), COUNT(*) TotalCount
FROM jennina’s_table name
GROUP BY jennina’s column
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

after that you'll just have to subtract total count and duplicate count, and voila, your total is counted without duplicate rows.

Hope this helps, if you have any question regarding this don’t be afraid to ask!

About Miniaoh Pultu

Questions
1

Answers
28

Best Answers
8

• Vote Up 0 Vote Down

• Posted on - 03/08/2012
• Question Category: MS SQL Server

Best Answer

Best Answer

Answered By Sharath Reddy 590495 points N/A #90546

How to get the total number of records without duplicating

 

Almost all of the posts I saw on the web discuss the method of counting the number of records in the database using the duplicate ones. And since you only want unique results in your query this is not appropriate for you. I found an example which is very similar to your inquiry and was able to solve it using 2 methods of counting the records or counting the sum of the rows without the duplicates.

The sample above has “CustomerID” with 6 records in it. It contains 2 duplicates which supposedly will return 4 unique records only ignoring the duplicates when doing the query. To do this, any of these 2 methods can be used:

About Sharath Reddy

Questions
1

Answers
14599

Best Answers
2290

• Vote Up 0 Vote Down

• Posted on - 05/03/2013
• Question Category: MS SQL Server

Answered By jennina yale 20 points N/A #90547

How to get the total number of records without duplicating

Well, it’s really hard to keep the records retained without duplicating ones in a database. Now I have got the easiest technique to keep the record with an easy command. Thanks Sharath, for helping me with these command lines. It has made my work faster and smoother. You provided a simple way on how to manage and keep the records without any duplication that happens. Now I can generate a record file easily. So easy that I couldn’t ever imagine how I have done it! I spent about a week in thinking about this performance and now I don’t have to!

About jennina yale

Questions
28

Answers
6

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 08/24/2013
• Question Category: MS SQL Server