STRING_AGG gives wrong output for string values

Question

I am executing a query that will fetch me the name of the doctor,the total number of patients that visited the doctor, the number of offices he went, based on the number of offices he went List out the name of the offices

The query is as follows:

SELECT DOCTOR_WORK.user_label AS "USER_LABEL",
  -- COUNT(DOCTOR_WORK.WEEKEND_WORKING),
   COUNT(DOCTOR_WORK.CONSULT_ID) AS "PATIENT_COUNT",
   count(DISTINCT(DOCTOR_WORK.OFFICE_ID)) AS "OFFICES_DOCTOR_WORKED",
   STRING_AGG(CASE 
          WHEN (DOCTOR_WORK.OFFICE_ID)=4 THEN 'HQ' 
          WHEN (DOCTOR_WORK.OFFICE_ID)=5 THEN 'TCM'
          WHEN (DOCTOR_WORK.OFFICE_ID)=6 THEN 'TCD'
          WHEN (DOCTOR_WORK.OFFICE_ID)=7 THEN 'SCM'
          WHEN (DOCTOR_WORK.OFFICE_ID)=8 THEN 'SSAAC'   
          ELSE 'No branch assigned. Check with Admin'
          END,',') AS "OFFICE_DOCTOR_WORKED_LIST"   
  FROM 
  (
   SELECT
  a.CONSULT_DATE,
  YEAR(a.CONSULT_DATE),
  MONTHNAME(a.CONSULT_DATE) AS "Month",
  DAYNAME(a.entered_date)AS "Day",
  CASE 
    WHEN a.OFFICE_ID=7 THEN 'WORKING'
    WHEN a.OFFICE_ID!=7 THEN 
        CASE when DAYNAME(a.consult_date) in ('FRIDAY','SATURDAY') THEN 'WEEKEND' ELSE 'WORKING' END
  END AS "WEEKEND_WORKING",
  a.CONSULT_ID,
  a.PATIENT_NAME,
  a.DOCTORS_ID,
  b.USER_LABEL,
  a.DEPARTMENT_ID,
  c.DEPARTMENT_NAME,
  a.OFFICE_ID,
  d.OFFICE_NAME
  FROM
  DOCTOR_CONSULT a
  INNER JOIN VIEW_USER_SETUP b
     ON (a.DOCTORS_ID=b.USER_ID)
  INNER JOIN DEPARTMENT_SETUP c
     ON (a.DEPARTMENT_ID=c.DEPARTMENT_ID)
  INNER JOIN OFFICE_DETAILS d
     ON (a.OFFICE_ID=d.OFFICE_ID)
  WHERE
    a.CONSULT_DATE
  BETWEEN '2022-12-30' AND '2023-01-10'
  AND
      b.ACTIVE_STATUS='Y'
  AND 
      d.ACTIVE_STATUS='Y'
  AND
      b.USER_LABEL NOT IN('Emergency Room','General Doctor','General Doctor Oph')
  AND b.USER_ID=318  
  ORDER BY a.consult_date
) AS "DOCTOR_WORK"
WHERE 
DOCTOR_WORK.WEEKEND_WORKING LIKE 'WORKING'
GROUP BY (DOCTOR_WORK.user_label);

The output that i am getting is

USER_LABEL      PATIENT _COUNT      OFFICE_DOCTOR_WORKED      OFFICE_DOCTOR_WORKED_LIST
---------------------------------------------------------------------------------------
 Doc A               98                       4                   SCM,SCM,TCM,TCM,.....

The above, the 4th column will display the data based on the count of patient. The output that i require is

USER_LABEL      PATIENT _COUNT      OFFICE_DOCTOR_WORKED      OFFICE_DOCTOR_WORKED_LIST
---------------------------------------------------------------------------------------
 Doc A               98                       4                   SCM,TCM,SSAC,HQ

The part of the sql code that i have issue is

 STRING_AGG(CASE 
          WHEN (DOCTOR_WORK.OFFICE_ID)=4 THEN 'HQ' 
          WHEN (DOCTOR_WORK.OFFICE_ID)=5 THEN 'TCM'
          WHEN (DOCTOR_WORK.OFFICE_ID)=6 THEN 'TCD'
          WHEN (DOCTOR_WORK.OFFICE_ID)=7 THEN 'SCM'
          WHEN (DOCTOR_WORK.OFFICE_ID)=8 THEN 'SSAAC'   
          ELSE 'No branch assigned. Check with Admin'
          END,',') AS "OFFICE_DOCTOR_WORKED_LIST"   

What is the change that is required in the SQL statement. is WITHIN GROUP required.If so how it must be entered in the above code. This is becuase i am getting error. The error type is "Incorrect Syntax"

Answer 1

I was able to solve this with the following code:

SELECT DOCTOR_WORK_LIST.USER_LABEL AS "USER_LABEL",
   SUM(DOCTOR_WORK_LIST.PATIENT_COUNT) AS "PATIENT_COUNT",
   SUM(DOCTOR_WORK_LIST.OFFICES_DOCTOR_WORKED) AS "COUNT_OFFICES_DOCTOR_WORKED",
   STRING_AGG(CASE 
          WHEN DOCTOR_WORK_LIST.OFFICE_DOCTOR_WORKED_LIST LIKE 'HQ'  THEN 'HeadQuarters-MultiSpeciality' 
          WHEN DOCTOR_WORK_LIST.OFFICE_DOCTOR_WORKED_LIST LIKE 'TCM' THEN 'Tahreer Camp-MultiSpeciality'
          WHEN DOCTOR_WORK_LIST.OFFICE_DOCTOR_WORKED_LIST LIKE 'TCD' THEN 'Tahreer Camp-Dental'
          WHEN DOCTOR_WORK_LIST.OFFICE_DOCTOR_WORKED_LIST LIKE 'SCM' THEN 'Summod Camp-MultiSpeciality'
          WHEN DOCTOR_WORK_LIST.OFFICE_DOCTOR_WORKED_LIST LIKE 'SSAAC'THEN 'Sheikh Salem Al Ali Camp'   
          ELSE 'No branch assigned. Check with Admin'
          END,',  ') AS "OFFICES_DOCTOR_WORKED_LIST"
 FROM 
(
 SELECT DOCTOR_WORK.user_label AS "USER_LABEL",
     COUNT(DOCTOR_WORK.CONSULT_ID) AS "PATIENT_COUNT",
     count(DISTINCT(DOCTOR_WORK.OFFICE_ID)) AS "OFFICES_DOCTOR_WORKED",
     CASE 
          WHEN (DOCTOR_WORK.OFFICE_ID)=4 THEN 'HQ'
          WHEN (DOCTOR_WORK.OFFICE_ID)=5 THEN 'TCM'
          WHEN (DOCTOR_WORK.OFFICE_ID)=6 THEN 'TCD'
          WHEN (DOCTOR_WORK.OFFICE_ID)=7 THEN 'SCM'
          WHEN (DOCTOR_WORK.OFFICE_ID)=8 THEN 'SSAAC'   
       ELSE 'No branch assigned. Check with Admin'
     END AS "OFFICE_DOCTOR_WORKED_LIST"          
FROM 
(
      SELECT
      a.CONSULT_DATE,
      YEAR(a.CONSULT_DATE),
      MONTHNAME(a.CONSULT_DATE) AS "Month",
      DAYNAME(a.entered_date)AS "Day",
      CASE 
        WHEN a.OFFICE_ID=7 THEN 'WORKING'
        WHEN a.OFFICE_ID!=7 THEN 
            CASE when DAYNAME(a.consult_date) in ('FRIDAY','SATURDAY') THEN 'WEEKEND' ELSE 'WORKING' END
      END AS "WEEKEND_WORKING",
      a.CONSULT_ID,
      a.PATIENT_NAME,
      a.DOCTORS_ID,
      b.USER_LABEL,
      a.DEPARTMENT_ID,
      c.DEPARTMENT_NAME,
      a.OFFICE_ID,
      d.OFFICE_NAME
      FROM
      DOCTOR_CONSULT a
      INNER JOIN VIEW_USER_SETUP b
         ON (a.DOCTORS_ID=b.USER_ID)
      INNER JOIN DEPARTMENT_SETUP c
         ON (a.DEPARTMENT_ID=c.DEPARTMENT_ID)
      INNER JOIN OFFICE_DETAILS d
         ON (a.OFFICE_ID=d.OFFICE_ID)
      WHERE
        a.CONSULT_DATE
      BETWEEN CURRENT_DATE AND CURRENT_DATE
      AND
          b.ACTIVE_STATUS='Y'
      AND 
          d.ACTIVE_STATUS='Y'
      AND
          b.USER_LABEL NOT IN('Emergency Room','General Doctor','General Doctor Oph')
     -- AND b.USER_ID=318  
      ORDER BY a.consult_date
  ) AS "DOCTOR_WORK"
WHERE 
DOCTOR_WORK.WEEKEND_WORKING LIKE 'WORKING'
GROUP BY 
DOCTOR_WORK.user_label,
DOCTOR_WORK.OFFICE_ID
) AS "DOCTOR_WORK_LIST"
GROUP BY(DOCTOR_WORK_LIST.USER_LABEl);