Find addresses shared by more than one customer_id where customer_name is is distinct
The query above groups by ADDRESS, CUSTOMER_ID and CUSTOMER_NAME. Since CUSTOMER_ID is unique, the output will have 1 row for each CUSTOMER_ID.

This query gives the correct result: 

SELECT
    CUSTOMER_ID = MIN(ct.CUSTOMER_ID)
    , ct.CUSTOMER_NAME
    , ct. ADDRESS
    , MEMBER_SINCE = MIN(ct.MEMBER_SINCE)
    , cnt.c
FROM CUSTOMER_TABLE ct
INNER JOIN (
    SELECT ADDRESS, COUNT(DISTINCT CUSTOMER_NAME) as c
    FROM CUSTOMER_TABLE 
    GROUP BY ADDRESS 
    HAVING COUNT(ADDRESS) &gt; 1
) as cnt ON cnt.ADDRESS = ct.ADDRESS
GROUP BY ct.ADDRESS, ct.CUSTOMER_NAME, cnt.c
ORDER BY MIN(ct.CUSTOMER_ID); 


It should group by ADDRESS andCUSTOMER_NAMEonly and pick one of theCUSTOMER_IDandMEMBER_SINCE. I choose theMIN(value)` here.

Since you also need the count of distinct CUSTOMER_ID with similar ADDRESS, it has been added to a subquery.

Result:

CUSTOMER_ID | CUSTOMER_NAME | ADDRESS           | MEMBER_SINCE              | COUNT
A1000       | John Doe      | 123 West Lane     | 2005-02-23 00:00:00.000   | 3
A1002       | Tom White     | 456 East Street   | 2006-05-07 00:00:00.000   | 4
A1003       | Frank Smith   | 123 West Lane     | 2006-12-12 00:00:00.000   | 3
A1004       | Lisa Simpson  | 456 East Street   | 2007-07-19 00:00:00.000   | 4
A1006       | Darth Vader   | 123 West Lane     | 2008-09-29 00:00:00.000   | 3
A1007       | John Doe      | 456 East Street   | 2007-05-23 00:00:00.000   | 4
A1008       | Bart Star     | 456 East Street   | 2006-04-19 00:00:00.000   | 4


Note that I don't have access to SAP-Hana. However it seems to be pretty standard SQL and with query should work on most RDBMS.