Forum Stats

  • 3,855,318 Users
  • 2,264,499 Discussions
  • 7,905,968 Comments

Discussions

Aggregration

jvjmohan
jvjmohan Member Posts: 19
edited May 15, 2014 2:44PM in SQL & PL/SQL

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

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited May 14, 2014 10:27PM Answer ✓

    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?

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,510 Red Diamond
    edited May 14, 2014 10:27PM Answer ✓

    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?

  • 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

    michaelrozar17
  • jvjmohan
    jvjmohan Member Posts: 19
This discussion has been closed.