Aggregration

jvjmohan

Hi All,

In our DB we have multiple tables say T1, T2 each have diff no. of columns T1 (A,B) and T2 (X,Y,Z) in each.

Each columns have 3 types of data 1,0 and any other number. I need the count of all 0,1 in all tables.

T1

-----

A B

0 1

1 0

0 x

1 1

T2

------

X Y Z

1 0 1

1 1 X

0 0 0

X X 1

1 1 1

Output should be

        0 1

T1 A 2  2

T1 B 1  2

T2 X  1  3

T2 Y 2  2

T2 3 1  3

MOST important, each table will have millions of records and we have query the table only once.

If any more information please let me know.

Thanks,

Vijay

Frank Kulash

Hi, Vijay,

Here's one way:

WITH    unpivoted_data    AS
(
    SELECT    'T1'   AS table_name
    ,         column_name
    ,         val
    FROM      t1
    UNPIVOT   (    val
              FOR  column_name  IN (a, b)
              )
    WHERE     val   IN ('0', '1')
UNION ALL
    SELECT    'T2'   AS table_name
    ,         column_name
    ,         val
    FROM      t2
    UNPIVOT   (    val
              FOR  column_name  IN (x, y, z)
              )
    WHERE     val   IN ('0', '1')
)
SELECT    *
FROM      unpivoted_data
PIVOT     (  COUNT (*)
          FOR  val  IN ( '0'  AS cnt_0
                       , '1'  AS cnt_1
                       )
          )
ORDER BY  table_name
,         column_name
;

I'm curious: what is the business problem you need to solve here?

Frank Kulash

Hi, Vijay,

Here's one way:

WITH    unpivoted_data    AS
(
    SELECT    'T1'   AS table_name
    ,         column_name
    ,         val
    FROM      t1
    UNPIVOT   (    val
              FOR  column_name  IN (a, b)
              )
    WHERE     val   IN ('0', '1')
UNION ALL
    SELECT    'T2'   AS table_name
    ,         column_name
    ,         val
    FROM      t2
    UNPIVOT   (    val
              FOR  column_name  IN (x, y, z)
              )
    WHERE     val   IN ('0', '1')
)
SELECT    *
FROM      unpivoted_data
PIVOT     (  COUNT (*)
          FOR  val  IN ( '0'  AS cnt_0
                       , '1'  AS cnt_1
                       )
          )
ORDER BY  table_name
,         column_name
;

I'm curious: what is the business problem you need to solve here?

michaelrozar17

If your DB version is 10g or lesser then you could use CASE or DECODE statements and in such cases tables has to be accessed more than once depending on the number of columns you need to pivot, like..

select tabl,col,count(val0)cnt_0,count(val1) cnt_1

    from

        (select 'T1' tabl,'A' col

              ,case when a='0' then '0' end val0

              ,case when a='1' then '1' end val1     

            from t1 

        union all

        select 'T1' tabl,'B' col

              ,case when b='0' then '0' end val0

              ,case when b='1' then '1' end val1

            from t1

        union all          

        select 'T2' tabl,'X' col

              ,case when x='0' then '0' end val0

              ,case when x='1' then '1' end val1     

            from t2 

        union all

        select 'T2' tabl,'Y' col

              ,case when y='0' then '0' end val0

              ,case when y='1' then '1' end val1

            from t2

        union all

        select 'T2' tabl,'Z' col

              ,case when z='0' then '0' end val0

              ,case when z='1' then '1' end val1

            from t2     

        )  

    group by col,tabl

    order by tabl,col

jvjmohan

Thanks a lot.