Home » SQL & PL/SQL » SQL & PL/SQL » please check the query
please check the query [message #314622] Thu, 17 April 2008 05:38 Go to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
please check the below query, iam not able to do count.it showing error.
the error is:selected non-aggregate values must be part of the associated group
select distinct
DUV_MAIN.EMPLOYEE.PERSON_ID,DUV_MAIN.EMPLOYEE.NSP_CD,
count(CASE WHEN DUV_MAIN.HR_PERF_APPRAISAL.APPRAISAL_TYPE_CD = 'PERFORMANCE MANAGEMENT' AND DUV_MAIN.HR_PERF_APPRAISAL.APPRAISAL_STATUS_CD = 'OSCO' AND DUV_MAIN.HR_PERF_ASSESSABLE_OBJ.OBJECTIVE_WHAT_TXT IS NOT NULL THEN 1 ELSE 0 END),
count(CASE WHEN DUV_MAIN.HR_PERF_APPRAISAL.APPRAISAL_TYPE_CD = 'PERFORMANCE MANAGEMENT' AND DUV_MAIN.HR_PERF_APPRAISAL.APPRAISAL_STATUS_CD = 'MYRC' THEN 1 ELSE 0 END),
count(CASE WHEN DUV_MAIN.HR_PERF_APPRAISAL.APPRAISAL_TYPE_CD = 'PERFORMANCE MANAGEMENT' AND DUV_MAIN.HR_PERF_APPRAISAL.APPRAISAL_STATUS_CD = 'YERC' THEN 1 ELSE 0 END),
count(DUV_MAIN.EMPLOYEE.PERSON_ID) total_people
from
DUV_MAIN.HR_PERF_ASSESSABLE_OBJ right join
DUV_MAIN.HR_PERF_APPRAISAL on
DUV_MAIN.HR_PERF_APPRAISAL.APPRAISAL_ID=DUV_MAIN.HR_PERF_ASSESSABLE_OBJ.AO_APPRAISAL_ID
where
DUV_MAIN.EMPLOYEE.PRIMARY_APPOINTMENT_IND= 'Y'
and
1080417 BETWEEN DUV_MAIN.EMPLOYEE.EMPLOYEE_EFFECTIVE_START_DT AND DUV_MAIN.EMPLOYEE.EMPLOYEE_EFFECTIVE_END_DT
and
DUV_MAIN.EMPLOYEE.PERSON_ID=DUV_MAIN.HR_PERF_APPRAISAL.APPRAISEE_PERSON_ID
order by
NSP_CD
Re: please check the query [message #314627 is a reply to message #314622] Thu, 17 April 2008 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: please check the query [message #314632 is a reply to message #314622] Thu, 17 April 2008 05:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You will need a GROUP BY clause on the query. You will need to group by all the columns in the SELECT list that do not use any aggregate function (like COUNT).
Re: please check the query [message #314638 is a reply to message #314622] Thu, 17 April 2008 06:01 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

count(CASE WHEN DUV_MAIN.HR_PERF_APPRAISAL.APPRAISAL_TYPE_CD = 'PERFORMANCE MANAGEMENT' AND DUV_MAIN.HR_PERF_APPRAISAL.APPRAISAL_STATUS_CD = 'OSCO' AND DUV_MAIN.HR_PERF_ASSESSABLE_OBJ.OBJECTIVE_WHAT_TXT IS NOT NULL THEN 1 ELSE 0 END),
count(CASE WHEN DUV_MAIN.HR_PERF_APPRAISAL.APPRAISAL_TYPE_CD = 'PERFORMANCE MANAGEMENT' AND DUV_MAIN.HR_PERF_APPRAISAL.APPRAISAL_STATUS_CD = 'MYRC' THEN 1 ELSE 0 END),
count(CASE WHEN DUV_MAIN.HR_PERF_APPRAISAL.APPRAISAL_TYPE_CD = 'PERFORMANCE MANAGEMENT' AND DUV_MAIN.HR_PERF_APPRAISAL.APPRAISAL_STATUS_CD = 'YERC' THEN 1 ELSE 0 END),

Please follow the guidelines as Michael already suggested. Also above statement is of no use because
  1* select deptno, job from emp order by deptno
SQL> /

    DEPTNO JOB
---------- ---------
        10 MANAGER
        10 CLERK
        10 PRESIDENT
        20 ANALYST
        20 ANALYST
        20 MANAGER
        20 CLERK
        20 CLERK
        30 SALESMAN
        30 SALESMAN
        30 SALESMAN
        30 CLERK
        30 SALESMAN
        30 MANAGER

14 rows selected.

  1  select deptno, count(case when job = 'PRESIDENT' then 1 else 0 end) president_count,
  2                 count(case when job = 'MANAGER' then 1 else 0 end) manager_count,
  3                 count(*) number_of_emp
  4  from emp
  5* group by deptno
SQL> /

    DEPTNO PRESIDENT_COUNT MANAGER_COUNT NUMBER_OF_EMP
---------- --------------- ------------- -------------
        10               3             3             3
        20               5             5             5
        30               6             6             6


  1  select deptno, count(case when job = 'PRESIDENT' then 1 else null end) president_count,
  2                 count(case when job = 'MANAGER' then 1 else null end) manager_count,
  3                 count(*) number_of_emp
  4  from emp
  5* group by deptno
SQL> /

    DEPTNO PRESIDENT_COUNT MANAGER_COUNT NUMBER_OF_EMP
---------- --------------- ------------- -------------
        10               1             1             3
        20               0             1             5
        30               0             1             6

Regards

Raj
Re: please check the query [message #314662 is a reply to message #314638] Thu, 17 April 2008 07:32 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
S.Rajaram
Also above statement is of no use because ...

Right; although, with a slight modification (COUNT -> SUM) it would do the job:
SQL> select
  2    deptno,
  3    sum(case when job = 'PRESIDENT' then 1 else 0 end) president_count,
  4    sum(case when job = 'MANAGER' then 1 else 0 end) manager_count,
  5    count(*) number_of_emp
  6  from emp
  7  group by deptno
  8  order by deptno;

    DEPTNO PRESIDENT_COUNT MANAGER_COUNT NUMBER_OF_EMP
---------- --------------- ------------- -------------
        10               1             1             3
        20               0             1             5
        30               0             1             6

SQL>
Re: please check the query [message #314710 is a reply to message #314662] Thu, 17 April 2008 09:49 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I agree you could use sum/count.I feel in this scenario/circumstance Count makes more logical sense to me than using Sum function. But people opinion differs.

Regards

Raj

P.S : I am not able to find the post from @Michael regarding the same discussion.
Previous Topic: to view log if reindexing done
Next Topic: Time taken for script
Goto Forum:
  


Current Time: Thu Dec 08 16:39:05 CST 2016

Total time taken to generate the page: 0.10655 seconds