CODEWITHSUNDEEP
sqloracle-database
mc88
mc88

Reputation: 81

Oracle - HAVING COUNT >1 not giving results with multiple columns

I have a query that finds duplicated values on table:

SELECT
  crl_id,
  COUNT(crl_id)
FROM
  crl_table
WHERE
  crl_id !=0
GROUP BY
 crl_id
  HAVING COUNT (crl_id) >1

this gives me results (if duplicated values exist):

crl_id: 15755  count:2

When I put additional column to my query (for example the id of primary key on this table):

SELECT
  pk_crl_id,
  crl_id,
  COUNT(crl_id)
FROM
  crl_table
WHERE
  crl_id !=0
GROUP BY
pk_crl_id, crl_id
  HAVING COUNT (crl_id) >1

No results are given, as if no duplicated values exits (and they do exists!). Please help.

Upvotes: 2

Views: 11545

Answers (3)

user5683823
user5683823

Reputation:

Another approach - producing one row per "duplicate" crl_id, with the corresponding PK values presented in a comma-separated list, is like this:

select   crl_id, count(crl_id) as ct, 
         listagg(pk_crl_id, ',') within group (order by pk_crl_id) as pk_list
from     crl_table
where    crl_id != 0
group by crl_id
having   count(crl_id) > 1
;

Upvotes: 0

GrabNewTech
GrabNewTech

Reputation: 641

Primary key is a unique. So If you add primary key in group by then never you will get the duplicate values. Try this query.

SELECT pk_crl_id 
 FROM crl_table
WHERE crl_id IN (SELECT crl_id
             FROM crl_table
            WHERE crl_id !=0
             GROUP BY crl_id
             HAVING COUNT (crl_id) >1);

Upvotes: 0

user330315
user330315

Reputation:

If you add the PK to the group by you can't get more then one row per group because the PK column is unique.

If you want additional columns but group by a subset, you can use a window function for that:

select *
from (
  select pk_crl_id,
         crl_id,
         count(*) over (partition by crl_id) as clr_id_count
  from crl_table
  where crl_id <> 0
) t
where clr_id_count > 1

Upvotes: 3

Related Questions