DB2 Query modification to remove duplicate values using LISTAGG function

I am using DB2 v9 and trying to get country values in comma seperated format using below query

SELECT  distinct LISTAGG(COUNTRIES, ',') WITHIN GROUP(ORDER BY EMPLOYEE)  
FROM LOCATION ;

Output Achieved 
MEXICO,UNITED STATES,INDIA,JAPAN,UNITED KINGDOM,MEXICO,UNITED STATES

The table "LOCATION" has multiple columns apart from Employee and Country and hence i cant group on countries to remove duplicate countries values .
Is there a way to remove dups to get only countries once using above query with modification .

Expected Output  ( Should exclude MEXICO,UNITED STATES as these two countries are duplicated) 
MEXICO,UNITED STATES,INDIA,JAPAN,UNITED KINGDOM

Any help appreciated.

You're mashing together at least two different sets of results grouped by employee, I think. Select employee and country, and you might get something like this:

1 MEXICO, UNITED STATES, INDIA, JAPAN, UNITED KINGDOM
2 MEXICO, UNITED STATES

If you just want all countries from all employees, just SELECT DISTINCT LISTAGG(COUNTRIES, ',') FROM LOCATION; may do.

Hi Corona688 Thank you for quick help on this . However I would need further info on the handling scenarios when we have multiple column in a table .

 
 SELECT
 Employee_ID 
 LISTAGG(COUNTRIES_OUT, ',') WITHIN GROUP(ORDER BY Employee_ID) AS "COUNTRIES_OUT" 
 FROM 
 LOCATION 
 GROUP BY 
 Employee_ID
 Output 
 
 Employee_ID ,               COUNTRIES_OUT 
 1234                    MEXICO,UNITED STATES, INDIA, JAPAN,UNITED KINGDOM,PHILIPPINES
 

The above observation is fine as I am using only one column "COUNTRIES_OUT" and grouped against one column "Employee_ID"
Now the challenge is when I add new column to replicate same logic

 
 SELECT
 Employee_ID 
 LISTAGG(COUNTRIES_OUT, ',') WITHIN GROUP(ORDER BY Employee_ID) AS "COUNTRIES_OUT" ,
 LISTAGG(COUNTRIES_IN, ',') WITHIN GROUP(ORDER BY Employee_ID) AS "COUNTRIES_IN"  ( This is newly added which has only one country ) 
 FROM 
 LOCATION 
 GROUP BY 
 Employee_ID
 Output 
 Employee_ID ,                                                    COUNTRIES_OUT                                                                                                                    COUNTRIES_IN 
 1234                  MEXICO,UNITED STATES, INDIA, JAPAN,UNITED KINGDOM,PHILIPPINES                                              UNITED STATES,UNITED STATES,UNITED STATES,UNITED STATES,UNITED STATES,UNITED STATES
 

The above output give 6 times UNITED STATES for column COUNTRIES_IN even though it appears once .

 
 How can I get the output like below 
 
 Employee_ID ,                                                    COUNTRIES_OUT                                                                                                                    COUNTRIES_IN 
 1234                  MEXICO,UNITED STATES, INDIA, JAPAN,UNITED KINGDOM,PHILIPPINES                                                                                     UNITED STATES
 

Please guide . any help appreciated

I don't think you should specify "WITHIN GROUP" more than once, that restriction would end up applying to all columns, put it at the end.

Thanks Corona688 . I created a sub query and called separately for those 2 columns and it worked . Thank you for suggestion Cheers