error of finding distinct cobinations of muiltiple columns in IBM netezza SQL table

1
votes

I need to count a distinct combination number for multiple columns in SQL IBM netteza Aiginity workbench.

 id1   id2    id3    id4  id5   id6
 NY    63689  eiof   394  9761   9318
 NY    63689  secf   064  9742   78142
 CT    39631  pfef  92169 9418   9167
 CT    39631  bfbde  23457 10052  618146

The result should be 

 id1   id2    value
 NY    63689  2   
 CT    39631  2

I need to find how many distinct combinations of id3,id4,id5 ,id6 for each distinct id2 and id2.

My sql query : 

 SELECT id1, id2,  
   ( 
      SELECT count(*)  as dis_id_by_id1_id2
      from 
      (
        select distinct id3 , id4 ,id5 ,id6
        FROM my_table 
        group by id1, id2 
      ) t
   ) 
   from my_table
   group by id1, id2

I got error: 

   id3 must be GROUPed or used in an aggregate function

If I grouped id3, id4, id5, id6. the result is wrong.

count(distinct id3, id4) is not allowed in IBM netezza.

Any help would be appreciated.

2
sqlsql-serverdatabase

2 Answers

1
votes

If you first write a query to generate the unique combinations, then you can use it as a nested sub-query.

Then it's a simple GROUP BY and COUNT().

SELECT
  id1,
  id2,
  COUNT(*)   AS value
FROM
(
  SELECT
    id1, id2, id3, id4, id5, id6
  FROM
    myTable
  GROUP BY
    id1, id2, id3, id4, id5, id6
)
  AS uniques
GROUP BY
  id1,
  id2
1
votes

In Postgres, you can do this with arrays and counts:

select id1, id2,
       count(distinct string_to_array(id3::text, '') || id4::text || id5::text || id6::text)
from table t
group by id1, id2;