Geeting count of data from 2 diff table swhich dont have common columns in it

Database

Geeting count of data from 2 diff table swhich dont have common columns in it

HI

I have two tables a and b. Both tables dont have any common columns in them

I need to get the count of the data which is union of a and b tables data
i.e Count C = Count a + Count b

How do I write a query for this .In B most of the column are derived columns from other reference tables

Thanks
Sirisha
3 REPLIES
Teradata Employee

Re: Geeting count of data from 2 diff table swhich dont have common columns in it

Hello,

You can use following:

SELECT SUM(CountTables) FROM
(
SELECT COUNT(*) AS "CountTables" FROM Table1
UNION
SELECT COUNT(*) AS "CountTables" FROM Table2
) Alias1;

HTH.

Regards,

Adeel

Re: Geeting count of data from 2 diff table swhich dont have common columns in it

Thanks Adeel

Its working ...
Enthusiast

Re: Geeting count of data from 2 diff table swhich dont have common columns in it

Make it UNION ALL, not just UNION - if both Table1 and Table2 have the same number of records UNION will eliminate one of the COUNT(*)s (treating one of them as a duplicate) and you will get the wrong result.

For instance:

-- result: 2
SELECT COUNT(1) AS no_of_records FROM DBC.Dbcinfo;

-- result: 2
SELECT
SUM(u.no_of_records) AS total_no_of_records
FROM
(
SELECT COUNT(1) AS no_of_records FROM DBC.Dbcinfo
UNION
SELECT COUNT(1) AS no_of_records FROM DBC.Dbcinfo
) u;

But:

-- result: 4
SELECT
SUM(ua.no_of_records) AS total_no_of_records
FROM
(
SELECT COUNT(1) AS no_of_records FROM DBC.Dbcinfo
UNION ALL
SELECT COUNT(1) AS no_of_records FROM DBC.Dbcinfo
) ua;