CODEWITHSUNDEEP
sql-server
Costa
Costa

Reputation: 4085

Calculating number of duplicated rows

I am confused why those queries are returning different number of records, please note that XXX table contains only three columns A,B,C

Query 1)

SELECT SUM (Counts) FROM 
    (
        SELECT Count(*) Counts FROM dbo.XXX 
              GROUP BY A,B,C
              HAVING COUNT(*) = 1
    ) T

Query 2)

SELECT A, B, C
  FROM [dbo].XXX
  GROUP BY A,B,C

Upvotes: 0

Views: 46

Answers (2)

Tobsey
Tobsey

Reputation: 3400

Query 1 is counting the number of records for which A, B & C are unique in the table.

Query 2 is giving you the unique combinations of A, B & C in the table.

Upvotes: 0

Randy Minder
Randy Minder

Reputation: 48402

Perhaps because your first query is only summing groups that have a group by count exactly equal to 1. Your second query will return all counts, whether the group by count is one or more.

So, it's possible that the combination of A, B and C occurs more than once. And if this is the case, your counts won't be the same.

Upvotes: 3

Related Questions