how to count no of rows from all the tables ?

vyasricha · New User Joined: 17 Mar 2008 Posts: 27 Location: banaglore

Hi ,

I am trying to count no of rows from two tables as:

SELECT SUM(C.CNTR) FROM (
SELECT ID,COUNT(*) AS CNTR
FROM TABLEA ,
TABLEB
WHERE AN_ID = BN_ID
AND AN_MTH = 01
GROUP BY AN_ID
HAVING SUM(AN_AMNT) > 100 ) C;

i am getting errors as (*) is not valid . Please suggest.

Pandora-Box · Posted: Thu May 31, 2012 9:08 am

I understand you wanted to count number of records which is present in table for the below where condition & having

vyasricha · New User Joined: 17 Mar 2008 Posts: 27 Location: banaglore

Thanks for your reply, this will give me only count per group but i want to sum of count - for all rows.

vyasricha · New User Joined: 17 Mar 2008 Posts: 27 Location: banaglore

HI All,

The below query is working fine :
:
SELECT SUM(C.CNTR) FROM (
SELECT A.ID,COUNT(*) AS CNTR
FROM TABLEA ,
TABLEB
WHERE AN_ID = BN_ID
AND AN_MTH = 01
GROUP BY AN_ID
HAVING SUM(AN_AMNT) > 100 ) C;

Thanks for your help !

Anuj Dhawan · Posted: Thu May 31, 2012 1:52 pm

So using "A.ID" instead of "ID" for Table-A solved the problem?