Multiple occurrences of a field based on another field

Question

I have a table Accounts which is having various fields. Out of which I want three fields acct_nbr, Acct_Indicator1, Acct_Indicator2. I want to find those accounts which are having multiple values for Acct_Indicator1 and Acct_Indicator2. Below is the query I have used. Please let me know the correct version :

SELECT acct_nbr, Acct_Indicator1, Acct_Indicator2 
FROM table1
GROUP BY acct_nbr, Acct_Indicator1, Acct_Indicator2
ORDER BY acct_nbr

Answer 1

try this using in for Acct_Indicator1 and Acct_Indicator2

SELECT acct_nbr
    ,Acct_Indicator1
    ,Acct_Indicator2
FROM table1
WHERE Accnt_Indicator1 IN (
        SELECT CASE 
                WHEN count(Accnt_Indicator1) > 1
                    THEN Accnt_Indicator1
                END AS col1
        FROM table1
        GROUP BY acct_nbr
        )
    AND Accnt_Indicator2 IN (
        SELECT CASE 
                WHEN count(Accnt_Indicator2) > 1
                    THEN Accnt_Indicator2
                END AS col2
        FROM table1
        GROUP BY acct_nbr
        )
GROUP BY acct_nbr
    ,Acct_Indicator1
    ,Acct_Indicator2
ORDER BY acct_nbr