1 Reply Latest reply: Apr 26, 2013 12:33 AM by HamidHelal RSS

    ORACLE QUERY PROBLEM

    968357
      Dear All,

      i have to calulate percentage of attendence having a mannual flag i.e. 'Y' on datewise and sum up it with all existing dates and then devide it by no of all distinct date in a month for ex..

      *percentage=

      .......... Count no of attandence having ismannual flag set to be 'Y' on particular date
      SUM( ---------------------------------------------------------------------------------------------------------------*100)/Count(Distinct date in a month for a particular department)
      ........... count total no of candidate present on particular date*

      i have to calculate pertantage for each distinct department availabel in table tbltimeregister1

      for this i m trying query..
      select 
      DEPARTMENTCODE,sum(sum(decode(ismannual,'Y',1,0))/count(paycode)*100)/COUNT(DISTINCT DATEOFFICE)
      from tbltimeregister1
      where to_char(dateoffice,'MM/YYYY')='03/2013'
      GROUP BY (DEPARTMENTCODE,DATEOFFICE)
      but it display error not a single group fuction

      pl tell me how to calculate it for all the distinct department


      with regards
      vishal agrawal
        • 1. Re: ORACLE QUERY PROBLEM
          HamidHelal
          965354 wrote:
          Dear All,

          i have to calulate percentage of attendence having a mannual flag i.e. 'Y' on datewise and sum up it with all existing dates and then devide it by no of all distinct date in a month for ex..

          *percentage=

          .......... Count no of attandence having ismannual flag set to be 'Y' on particular date
          SUM( ---------------------------------------------------------------------------------------------------------------*100)/Count(Distinct date in a month for a particular department)
          ........... count total no of candidate present on particular date*

          i have to calculate pertantage for each distinct department availabel in table tbltimeregister1

          for this i m trying query..
          select 
          DEPARTMENTCODE,sum(sum(decode(ismannual,'Y',1,0))/count(paycode)*100)/COUNT(DISTINCT DATEOFFICE)
          from tbltimeregister1
          where to_char(dateoffice,'MM/YYYY')='03/2013'
          GROUP BY (DEPARTMENTCODE,DATEOFFICE)
          but it display error not a single group fuction

          pl tell me how to calculate it for all the distinct department
          Hello, vishal agrawal
          Ops! post in wrong forum. please post {forum:id=75} and close this thread. Hope this helps