## Forum Stats

• 3,855,318 Users
• 2,264,499 Discussions

Discussions

# Aggregration

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.

Thanks,

Vijay

Tagged:

• 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?

• 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

• Thanks a lot.

This discussion has been closed.