Results 1 to 4 of 4

Thread: Query

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Query

    Hi guys

    I have a 9i database which receives counter data every 15 min.

    "Counter1+Counter2+Counter3+Counter4=KPI1"

    I was wanting to create a query which would give me KPI1 on an hourly basis.

    something like:

    Code:
    select trunc(datetime,'hh24') datetime, bsc, cell,
    SUM(NVL(counter1,0)) counter1+SUM(NVL(counter2,0)) counter2+SUM(NVL(counter3,0))counter3+SUM(NVL(counter4,0)) counter4 as KPI1
    
    from schema.table
    
    where SUM(NVL(counter1,0)) counter1+SUM(NVL(counter2,0)) counter2+SUM(NVL(counter3,0))counter3+SUM(NVL(counter4,0)) counter4  > 15
    
    group by trunc(datetime,'hh24')
    But I can't use SUM in the where clause.

    I understand SUM(NVL(counter1,0)) will add up all 4 quarters

    Regards

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Here's an example of how to do it. This is a query which selects data from Scott's EMP table - summary of SAL and COMM columns per department number:
    Code:
    SQL> select deptno, sum(nvl(sal, 0)) + sum(nvl(comm, 0)) sal_plus_comm
      2  from emp
      3  group by deptno;
    
        DEPTNO SAL_PLUS_COMM
    ---------- -------------
            30         16400
            20         14875
            10         11150
    Now let's assume that we'd like to select only departments where the money is greater than 15000 (this is, basically, your query which doesn't work):
    Code:
    SQL> select deptno, sum(nvl(sal, 0)) + sum(nvl(comm, 0)) sal_plus_comm
      2  from emp
      3  where sum(nvl(sal, 0)) + sum(nvl(comm, 0)) > 15000
      4  group by deptno;
    where sum(nvl(sal, 0)) + sum(nvl(comm, 0)) > 15000
          *
    ERROR at line 3:
    ORA-00934: group function is not allowed here
    So put the group function into the HAVING clause!
    Code:
    SQL> select deptno, sum(nvl(sal, 0)) + sum(nvl(comm, 0)) sal_plus_comm
      2  from emp
      3  group by deptno
      4  having sum(nvl(sal, 0)) + sum(nvl(comm, 0)) > 15000;
    
        DEPTNO SAL_PLUS_COMM
    ---------- -------------
            30         16400
    
    SQL>
    Now try to apply it to your problem.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by shajju View Post
    But I can't use SUM in the where clause.
    Conditions on groups are written using the HAVING clause:
    Code:
    SELECT trunc(datetime,'hh24') datetime, 
           bsc, 
           cell,
           SUM(NVL(counter1,0)) counter1+SUM(NVL(counter2,0)) counter2+SUM(NVL(counter3,0))counter3+SUM(NVL(counter4,0)) counter4 as KPI1
    FROM schema.table
    GROUP BY trunc(datetime,'hh24')
    HAVING SUM(NVL(counter1,0)) counter1+SUM(NVL(counter2,0)) counter2+SUM(NVL(counter3,0))counter3+SUM(NVL(counter4,0)) counter4  > 15

  4. #4
    Join Date
    Aug 2008
    Posts
    464

    Thanks

    Appreciated very much...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •