Oracle sql count of join gives me error of 00904. 00000 - "%s: invalid identifier

Question

Here is my query which tries to get how many CUSTOMER_IDs listed in the CUSTOMERS table are already in the TRANSACTIONS table too.

SELECT
    DISTINCT COUNT(*)
FROM
    (
        SELECT
            CUSTOMERS.CUSTOMER_ID
        FROM
            CUSTOMERS
        INNER JOIN TRANSACTIONS 
        ON CUSTOMERS.CUSTOMER_ID = TRANSACTIONS.CUSTOMER_ID
    )

This query returns

904. 00000 - "%s: invalid identifier error.

How can i fix my query?

Answer 1

A count(*) will always return a single number, so doing a distinct on it is unnecessary. It sounds like you want something more like:

SELECT COUNT( DISTINCT CUSTOMERS.CUSTOMER_ID )
  FROM CUSTOMERS
 INNER JOIN TRANSACTIONS 
         ON CUSTOMERS.CUSTOMER_ID = TRANSACTIONS.CUSTOMER_ID