Search This Blog

Showing posts with label number of rows in multiple tables. Show all posts
Showing posts with label number of rows in multiple tables. Show all posts

Wednesday, March 17, 2010

getting count of data from n different tables in teradata

I have n tables .
all tables need not to have
1.same number of columns
2.common columns.

I need to get the count of the data which is union of all the table's data.
for example there are 3 tables a,b,c in which
table 'a' has 3 rows
table 'b' has 4 rows.
table 'c' has 5 rows.

then Result Should be Count of a +
Count of b + Count of c
(i.e) 3+4+5 = 12

Ans :
I am doing this by taking 3 tables as example.
we can extend this for any number tables;

select * from a;

*** Query completed. 3 rows found. One column returned.
*** Total elapsed time was 1 second.

col1
-----------
3
1
2

select * from b;

*** Query completed. 4 rows found. 2 columns returned.
*** Total elapsed time was 1 second.

col1 col2
----------- -----------
3 3
1 1
4 4
2 2


select * from c;

*** Query completed. 5 rows found. 3 columns returned.
*** Total elapsed time was 1 second.

col1 col2 col3
----------- ----------- -----------
5 5 5
3 3 3
1 1 1
4 4 4
2 2 2



SELECT SUM(CountTables) FROM
(
SELECT COUNT(*) AS "CountTables" FROM a
UNION
SELECT COUNT(*) AS "CountTables" FROM b
UNION
SELECT COUNT(*) AS "CountTables" FROM c
)t;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Sum(CountTables)
----------------
12