SQL: How to find disparate values in columns and show their count?

Question

I have the following Oracle SQL table, where product_id 101 and 103 have disparate values in columns attr1 and attr3 respectively.

data

| PRODUCT_ID | ATTR1 | ATTR2 | ATTR3 |
|------------|-------|-------|-------|
| 101        | a     | x     | z     |
| 101        | a     | x     | zzz   |
| 101        | aa    | x     | z     |
| 102        | b     | y     | z     |
| 102        | b     | y     | z     |
| 103        | c     | z     | z     |
| 103        | c     | z     | zz    |

I want to get the following output which lists the disparate values and their count in the columns.

output

| PRODUCT_ID | DESCR            | VALUE_COUNT  |
|------------|------------------|--------------|
| 101        | Issue with attr1 | a(2), aa(1)  |
| 101        | Issue with attr3 | z(2), zzz(1) |
| 103        | Issue with attr3 | z(1), zz(1)  |

I wrote a query to get the result for just one column, but it will require quite an effort to write it for the actual data where I will need to check 20+ columns for disparate values. Any suggestions to make it more efficient?

query

WITH data AS (
   
   SELECT 101 product_id, 'a'    attr1, 'x'  attr2, 'z'     attr3    FROM dual UNION ALL
   SELECT 101 product_id, 'a'    attr1, 'x'  attr2, 'zzz'   attr3    FROM dual UNION ALL
   SELECT 101 product_id, 'aa'   attr1, 'x'  attr2, 'z'     attr3    FROM dual UNION ALL
   SELECT 102 product_id, 'b'    attr1, 'y'  attr2, 'z'     attr3    FROM dual UNION ALL
   SELECT 102 product_id, 'b'    attr1, 'y'  attr2, 'z'     attr3    FROM dual UNION ALL
   SELECT 103 product_id, 'c'    attr1, 'z'  attr2, 'z'     attr3    FROM dual UNION ALL
   SELECT 103 product_id, 'c'    attr1, 'z'  attr2, 'zz'    attr3    FROM dual
   
), d1 AS (

   SELECT product_id, 'Issue with attr1' descr 
   FROM data
   GROUP BY product_id 
   HAVING COUNT(DISTINCT attr1) > 1

), d2 AS (
      
   SELECT DISTINCT d1.product_id, d1.descr, data.attr1, COUNT(attr1) OVER (PARTITION BY attr1) cnt
   FROM d1
   INNER JOIN data
      ON d1.product_id = data.product_id
   
)

SELECT product_id, descr, LISTAGG(attr1 || '(' || cnt || ')', ', ') WITHIN GROUP (ORDER BY product_id) value_count
FROM d2
GROUP BY product_id, descr
;

Answer 1

You may unpivot all the attributes into separate rows, compute rowcount per attribute and value and compare it with the rowcount per product_id. Then aggregate errors back with listagg.

This will require only to add more columns to inpivot ... for ....

with prep as (
  select
    sample.*
    /*Rowcount per product_id*/
    , count(1) over(partition by product_id) as rowcnt
  from sample
)
, unp as (
  select
    product_id,
    /*Classify the issue*/
    case
      /*Count per value*/
      when count(1) != rowcnt
      then 'Issue with ' || col
    end as issue,
    /*Count per value*/
    coalesce(val, 'NULL') || '(' || count(1) || ')' as cnt
  from prep
  unpivot include nulls (
    val for col in (attr1, attr2, attr3)
  )
  group by
    product_id,
    col,
    val,
    rowcnt
)
/*Aggregate all*/
select
  product_id,
  issue,
  listagg(cnt, ', ') as value_count
from unp
where issue is not null
group by
  product_id,
  issue
order by 1, 2

PRODUCT_ID	ISSUE	VALUE_COUNT
101	Issue with ATTR1	a(2), aa(1)
101	Issue with ATTR3	z(2), zzz(1)
103	Issue with ATTR3	z(1), zz(1)

fiddle

UPD: By default unpivot removes nullvalues from output. To include them you need to addinclude nulls`.

If you add one more row eith null to your sample data:

insert into sample (PRODUCT_ID, ATTR1, ATTR2, ATTR3)
values (103, 'c', 'z', null)

the above (modified) query will return:

PRODUCT_ID	ISSUE	VALUE_COUNT
101	Issue with ATTR1	a(2), aa(1)
101	Issue with ATTR3	z(2), zzz(1)
103	Issue with ATTR3	z(1), zz(1), NULL(1)

fiddle

Answer 2

Try GROUPing first and then LISTAGG around that. Something like this:

WITH data AS (
   
   SELECT 101 product_id, 'a'    attr1, 'x'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 101 product_id, 'a'    attr1, 'x'  attr2, 'zzz'  attr3    FROM dual UNION ALL
   SELECT 101 product_id, 'aa'   attr1, 'x'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 102 product_id, 'b'    attr1, 'y'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 102 product_id, 'b'    attr1, 'y'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 103 product_id, 'c'    attr1, 'z'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 103 product_id, 'c'    attr1, 'z'  attr2, 'zz' attr3    FROM dual
 
)
SELECT product_id,'Issue with attr1' descr,LISTAGG(value_count,',') WITHIN GROUP (ORDER BY value_count) value_count
  FROM (SELECT product_id,
               attr1||'('||COUNT(*)||')' value_count
          FROM (SELECT product_id,
                       attr1,
                       COUNT(DISTINCT attr1) OVER (PARTITION BY product_id) cnt
                  FROM data)
         WHERE cnt > 1
         GROUP BY product_id,
                  attr1)
 GROUP BY product_id                  
UNION ALL                  
SELECT product_id,'Issue with attr3' descr,LISTAGG(value_count,',') WITHIN GROUP (ORDER BY value_count) value_count
  FROM (                  
        SELECT product_id,
               attr3||'('||COUNT(*)||')' value_count
          FROM (SELECT product_id,
                       attr3,
                       COUNT(DISTINCT attr3) OVER (PARTITION BY product_id) cnt
                  FROM data)
         WHERE cnt > 1
         GROUP BY product_id,
                  attr3)
  GROUP BY product_id

Answer 3

If you don't have too many attributes you can do:

WITH data AS (
   
   SELECT 101 product_id, 'a'    attr1, 'x'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 101 product_id, 'a'    attr1, 'x'  attr2, 'zz'  attr3    FROM dual UNION ALL
   SELECT 101 product_id, 'aa'   attr1, 'x'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 102 product_id, 'b'    attr1, 'y'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 102 product_id, 'b'    attr1, 'y'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 103 product_id, 'c'    attr1, 'z'  attr2, 'z'  attr3    FROM dual UNION ALL
   SELECT 103 product_id, 'c'    attr1, 'z'  attr2, 'zz' attr3    FROM dual
   
), count_ as (SELECT product_id, COUNT(DISTINCT attr1) attr1
  , COUNT(DISTINCT attr2) attr2
  , COUNT(DISTINCT attr3) attr3
   FROM data
   GROUP BY product_id ),
d2 AS (
      
   SELECT DISTINCT count_.product_id, 'Issue with attr1' descr , data.attr1, COUNT(data.attr1) OVER (PARTITION BY data.attr1) cnt
   FROM count_
   INNER JOIN data
      ON count_.product_id = data.product_id AND count_.attr1 > 1 
   UNION ALL
     SELECT DISTINCT count_.product_id, 'Issue with attr2' descr , data.attr2, COUNT(data.attr2) OVER (PARTITION BY data.attr2) cnt
   FROM count_
   INNER JOIN data
      ON count_.product_id = data.product_id AND count_.attr2 > 1 
  UNION ALL
     SELECT DISTINCT count_.product_id, 'Issue with attr3' descr , data.attr3, COUNT(data.attr3) OVER (PARTITION BY data.attr3) cnt
   FROM count_
   INNER JOIN data
      ON count_.product_id = data.product_id AND count_.attr3 > 1 
)
SELECT product_id, descr, LISTAGG(attr1 || '(' || cnt || ')', ', ') WITHIN GROUP (ORDER BY product_id) value_count
FROM d2
GROUP BY product_id, descr
;

PRODUCT_ID	DESCR	VALUE_COUNT
101	Issue with attr1	a(2), aa(1)
101	Issue with attr3	z(3), zz(2)
103	Issue with attr3	z(3), zz(2)

fiddle