count how many rows exist in another table with the same column-CodePudding

I have two tables with the same column named customer number ( CLNT_NO )

CLNT PROFIT ( CLNT_NO : ( 455, 713, 839, 455, 713, 839 ))

CLNT ACCOUNT ( CLNT_NO: ( 100, 713, 713, 713, 839, 100, 713, 713, 713, 839 839, 839 ) )

How many customers on the CLNT ACCOUNT table already exist on the CLNT PROFIT TABLE.

The result I am looking for this

count = 2

The code that I have been trying is :

SELECT COUNT (CLNT_NO) FROM CLNT_PROFIT WHERE EXISTS (SELECT CLNT_NO FROM CLNT_ACCT WHERE CLNT_ACCT.CLNT_NO=CLNT_PROFIT.CLNT_NO) GROUP BY CLNT_PROFIT.CLNT_NO

but the result I get is:

count 2, 2

CodePudding user response：

You should not group by CLNT_NO because you want 1 numeric result from the query and you should count the distinct values of CLNT_NO in the table CLNT_PROFIT:

SELECT COUNT(DISTINCT p.CLNT_NO) AS counter
FROM CLNT_PROFIT p
WHERE EXISTS (SELECT 1 FROM CLNT_ACCOUNT a WHERE a.CLNT_NO = p.CLNT_NO);

Or, with a join:

SELECT COUNT(DISTINCT p.CLNT_NO) AS counter
FROM CLNT_PROFIT p INNER JOIN CLNT_ACCOUNT a
ON a.CLNT_NO = p.CLNT_NO;

See the demo.

CodePudding user response：

You can simply inner join on the unique accounts.

A GROUP BY CLNT_NO ain't needed in the outer query.

SELECT COUNT(*) AS Total
FROM (SELECT DISTINCT CLNT_NO FROM CLNT_PROFIT) profit
JOIN (SELECT DISTINCT CLNT_NO FROM CLNT_ACCOUNT) account
  ON account.CLNT_NO = profit.CLNT_NO;

Total
2

db<>fiddle here