CODEWITHSUNDEEP
sqloracle-databaseselectcount
Jongu
Jongu

Reputation: 31

How to count distinct fields and calling it in the where statement?

I am trying to count the number of duplicates that appears on a table for example:

First| Last  | ADDR1           | City      | ST | Zip
-----+-------+-----------------+-----------+----+------
John | Smith | 1234 Fake St.   | Hollywood | CA | 12345
John | Smith | 1234 Fake St.   | Hollywood | CA | 12345
John | Smith | 1234 Fake St.   | Hollywood | CA | 12345
John | Smith | 1234 Fake St.   | Hollywood | CA | 12345
Jane | Smith | 1111 Junkertown | Phoenix   | AR | 22222
Jane | Smith | 1111 Junkertown | Phoenix   | AR | 22222
Jane | Smith | 1111 Junkertown | Phoenix   | AR | 22222

Here is my select statement however it is not liking my where statement. I only want to return rows with counts > 1

select distinct t.first_name, t.last_name, t.addr_line_1, t.city, 
                t.state_cd, t.zip, count(*) as numberofdupes
from name_addr t
where numberofdupes > 1
group by t.first_name, t.last_name, t.addr_line_1, t.city, t.state_cd, t.zip

If anyone can point me in the right direction. Please and thank you.

Upvotes: 0

Views: 85

Answers (3)

EzLo
EzLo

Reputation: 14189

If you want to filter by the results of the grouping, then you need to put your filters in the HAVING (after the GROUP BY) instead of the WHERE.

select t.first_name, t.last_name, t.addr_line_1, t.city, 
                t.state_cd, t.zip, count(*) as numberofdupes
from name_addr t
group by t.first_name, t.last_name, t.addr_line_1, t.city, t.state_cd, t.zip
HAVING count(*) > 1

You can also remove the DISTINCT if you are already grouping by all the columns.

Upvotes: 2

jarlh
jarlh

Reputation: 44776

Skip the WHERE clause, use HAVING for aggregate function conditions.

No need to do SELECT DISTINCT, the GROUP BY returns no duplicate rows.

select t.first_name, t.last_name, t.addr_line_1, t.city, 
       t.state_cd, t.zip, count(*) as numberofdupes
from name_addr t
group by t.first_name, t.last_name, t.addr_line_1, t.city, t.state_cd, t.zip
having count(*) > 1

Upvotes: 6

SQL_M
SQL_M

Reputation: 2475

select distinct t.first_name, t.last_name, t.addr_line_1, t.city, 
       t.state_cd, t.zip, count(*) as numberofdupes
from name_addr t
group by t.first_name, t.last_name, t.addr_line_1, t.city, t.state_cd, t.zip
HAVING COUNT(*) > 1

Upvotes: 1

Related Questions