not a GROUP BY expression error when using a function in a query

Question

I use a function in a query to select data but I have this error:

not a GROUP BY expression

This is the query:

SELECT PROF_EMPLOYEE_DATA.EMP_CODE, PROF_EMPLOYEE_DATA.EMP_NAME_AR, PROF_TREE_DETAILS.TREE_NAME, PROF_JOBS_DETAILS.JOB_NAME, PROF_EMPLOYEE_DATA.GROUP_START_DATE, 
 COUNT(DISTINCT ATT_EMPLOYEEATTENDANCE.ATTEND_DATE) AS WORKSDAYS
 ,nvl(getEmployee_OffDays ('" + dtp_PrintManth.Text + "',PROF_EMPLOYEE_DATA.SHIFT_ID),0) - GETEMPLOYEE_OFFDAYSWork(extract(month from to_date('" + dtp_PrintManth.Text + "','dd/mm/yyyy')),extract(year from to_date('" + dtp_PrintManth.Text + "','dd/mm/yyyy')),PROF_EMPLOYEE_DATA.EMPLOYEE_ID)as OffDays 
FROM PROF_EMPLOYEE_DATA INNER JOIN
 PROF_TREE_DETAILS ON PROF_EMPLOYEE_DATA.TREE_ID = PROF_TREE_DETAILS.TREE_ID INNER JOIN
 PROF_JOBS_DETAILS ON PROF_EMPLOYEE_DATA.JOB_ID = PROF_JOBS_DETAILS.JOB_ID LEFT OUTER JOIN
 ATT_EMPLOYEEATTENDANCE ON PROF_EMPLOYEE_DATA.EMPLOYEE_ID = ATT_EMPLOYEEATTENDANCE.EMPLOYEE_ID
WHERE (PROF_EMPLOYEE_DATA.EMP_CODE = '12411') AND (ATT_EMPLOYEEATTENDANCE.ATTEND_DATE BETWEEN TO_DATE('10/1/2016', 'MM/DD/YYYY') AND TO_DATE('10/31/2016', 'MM/DD/YYYY'))
GROUP BY PROF_EMPLOYEE_DATA.EMP_CODE, PROF_EMPLOYEE_DATA.EMP_NAME_AR, PROF_TREE_DETAILS.TREE_NAME, PROF_JOBS_DETAILS.JOB_NAME, PROF_EMPLOYEE_DATA.GROUP_START_DATE

And this is the function that I created to display days number in month:

create or replace
FUNCTION "GETEMPLOYEE_OFFDAYS" (month_p nvarchar2,shift_id_p number)
 RETURN decimal 
 IS VALUE_P decimal;
 VALUE1_P decimal;
 VALUE2_P decimal;
 i NUMBER := 0;
BEGIN
for RC in 
(SELECT LOK_DAYS.*
FROM ATT_SHIFTS
RIGHT JOIN ATT_SHIFTSDAYSOFF
ON ATT_SHIFTS.SHIFT_ID = ATT_SHIFTSDAYSOFF.SHIFT_ID
LEFT JOIN LOK_DAYS
ON LOK_DAYS.DAY_ID = ATT_SHIFTSDAYSOFF.DAY_ID
WHERE ATT_SHIFTS.SHIFT_ID = shift_id_p 
)
loop
i := i + 1;
IF i =1 THEN
 select
 count(to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDay')) into value_p
 from dual
 where to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_AR)
 OR to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_EN)
 connect by level<=to_char(last_day(to_date(month_p,'dd/mm/yyyy')),'dd') ;
 else if i=2 then
 select
 count(to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDay')) into value1_p
 from dual
 where to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_AR)
 OR to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_EN)
 connect by level<=to_char(last_day(to_date(month_p,'dd/mm/yyyy')),'dd') ;
 END IF;
 END IF;
END LOOP;
RETURN nvl(VALUE_P,0)+nvl(value1_p,0);
END;

• Mahmoud Omar: i do it but the i have the same error , my by group by don't accept function in select .

Answer 1

You can not just use any function in the SELECT list when you use GROUP BY.

You need to use an aggregate function (MIN, MAX, SUM, etc) on columns not included in the GROUP BY list, or include the columns used by a regular function in the GROUP BY list.

You use PROF_EMPLOYEE_DATA.SHIFT_ID and PROF_EMPLOYEE_DATA.EMPLOYEE_ID with your function, but they are not included in the GROUP BY list.

Including those columns in the GROUP BY list should solve this, but that may not provide the result you want.

Another way is to write your own aggregate function:

Using User-Defined Aggregate Functions