Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Unanswered: I beg you -- HELP!

    when I enter:
    SELECT INV_TRANSACTION.ITEM||'|'||INV_TRANSACTION.TRANS_D ATE||'|'||INV_TRANSACTION.FROM_WHS_CODE||'|'||WARE HOUSE.WHS_NAME||'|'||SUM(INV_TRANSACTION.QUANTITY)
    FROM ABC.INV_TRANSACTION INV_TRANSACTION, ABC.WAREHOUSE WAREHOUSE
    WHERE WAREHOUSE.WHS_CODE = INV_TRANSACTION.FROM_WHS_CODE
    AND INV_TRANSACTION.TRANS_DATE >= TO_DATE('20031222000000','YYYYMMDDHH24MISS')
    AND INV_TRANSACTION.TRANS_DATE <= TO_DATE('20031228000000','YYYYMMDDHH24MISS')
    AND INV_TRANSACTION.FROM_WHS_CODE = '5599AAA'
    AND INV_TRANSACTION.ITEM = '797553001'
    AND INV_TRANSACTION.TRANS_CODE = 'SI' AND INV_TRANSACTION.QUANTITY > 0
    GROUP BY INV_TRANSACTION.ITEM, INV_TRANSACTION.TRANS_DATE, INV_TRANSACTION.FROM_WHS_CODE, WAREHOUSE.WHS_NAME
    ORDER BY INV_TRANSACTION.TRANS_DATE ASC;
    I get:
    INV_TRANSACTION.ITEM||'|'||INV_TRANSACTION.TRANS_D ATE||'|'||INV_TRANSACTION.FROM_WHS_CODE||'|'||WARE
    ----------------------------------------------------------------------------------------------------
    797553001|22-DEC-03|5599AAA|Big Store-Sellable Inventory|5
    797553001|23-DEC-03|5599AAA|Big Store-Sellable Inventory|5
    797553001|26-DEC-03|5599AAA|Big Store-Sellable Inventory|20
    797553001|27-DEC-03|5599AAA|Big Store-Sellable Inventory|1
    what I really want is the Standard Deviation for the period so, I tried to modified the SUM(INV_TRANSACTION.QUANTITY) to be STDDEV(SUM(INV_TRANSACTION.QUANTITY) ) and get:
    ERROR at line 1:
    ORA-00937: not a single-group group function
    I figured it was missing an order by, so I added:
    ORDER BY stddev(SUM(INV_TRANSACTION.QUANTITY)) ASC
    at the end of the line.
    but now I get:
    *
    ERROR at line 10:
    ORA-00935: group function is nested too deeply
    I'm pullling my hair out. I see the data I want (5,5,20,1) and I know the Stddev works - but some some reason the Oracle gods only know... I am missing
    this one completely!! Please help!

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    62
    Have you tried stddev(colname) instead of
    stddev (sum(colname)) ?
    Regards,

    Rushi

  3. #3
    Join Date
    Jan 2004
    Posts
    4
    Originally posted by Rushi
    Have you tried stddev(colname) instead of
    stddev (sum(colname)) ?
    There are individual transactions in a day so they needed to be sum'd up, but I tried stddev(sum(INV_TRANSACTION.QUANTITY)) and I keep getting "not a single-grou group function)"...

  4. #4
    Join Date
    Jan 2004
    Location
    India
    Posts
    62
    Please write the final version of your query that you are running and still getting the error. i.e. query with the group by and order by clauses.
    Regards,

    Rushi

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by wiz4075
    There are individual transactions in a day so they needed to be sum'd up, but I tried stddev(sum(INV_TRANSACTION.QUANTITY)) and I keep getting "not a single-grou group function)"...
    I don't fully understand what you are trying to get here - do you want a single row result, being the standard deviations between the various SUMs? If so, do this:

    SELECT STDDEV(sum_qty) FROM
    (
    SELECT SUM(INV_TRANSACTION.QUANTITY) sum_qty
    FROM ABC.INV_TRANSACTION INV_TRANSACTION, ABC.WAREHOUSE WAREHOUSE
    WHERE WAREHOUSE.WHS_CODE = INV_TRANSACTION.FROM_WHS_CODE
    AND INV_TRANSACTION.TRANS_DATE >= TO_DATE('20031222000000','YYYYMMDDHH24MISS')
    AND INV_TRANSACTION.TRANS_DATE <= TO_DATE('20031228000000','YYYYMMDDHH24MISS')
    AND INV_TRANSACTION.FROM_WHS_CODE = '5599AAA'
    AND INV_TRANSACTION.ITEM = '797553001'
    AND INV_TRANSACTION.TRANS_CODE = 'SI' AND INV_TRANSACTION.QUANTITY > 0
    GROUP BY INV_TRANSACTION.ITEM, INV_TRANSACTION.TRANS_DATE, INV_TRANSACTION.FROM_WHS_CODE, WAREHOUSE.WHS_NAME
    );

    Or perhaps you want to SUM to one level and STDDEV to a higher level (but not just a single result)? Something like:

    SELECT ITEM||'|'||FROM_WHS_CODE||'|'||WHS_NAME||'|'||STDD EV(sum_qty)
    FROM
    (
    SELECT INV_TRANSACTION.ITEM,INV_TRANSACTION.TRANS_DATE,IN V_TRANSACTION.FROM_WHS_CODE,WAREHOUSE.WHS_NAME,SUM (INV_TRANSACTION.QUANTITY) sum_qty
    FROM ABC.INV_TRANSACTION INV_TRANSACTION, ABC.WAREHOUSE WAREHOUSE
    WHERE WAREHOUSE.WHS_CODE = INV_TRANSACTION.FROM_WHS_CODE
    AND INV_TRANSACTION.TRANS_DATE >= TO_DATE('20031222000000','YYYYMMDDHH24MISS')
    AND INV_TRANSACTION.TRANS_DATE <= TO_DATE('20031228000000','YYYYMMDDHH24MISS')
    AND INV_TRANSACTION.FROM_WHS_CODE = '5599AAA'
    AND INV_TRANSACTION.ITEM = '797553001'
    AND INV_TRANSACTION.TRANS_CODE = 'SI' AND INV_TRANSACTION.QUANTITY > 0
    GROUP BY INV_TRANSACTION.ITEM, INV_TRANSACTION.TRANS_DATE, INV_TRANSACTION.FROM_WHS_CODE, WAREHOUSE.WHS_NAME
    )
    GROUP BY ITEM,FROM_WHS_CODE,WHS_NAME;

    Or perhaps you meant something else entirely? In any case, applying 2 aggregate functions at once makes no sense - take this example:

    SQL> select deptno, sum(sum(sal)) sumsal
    2 from emp
    3 group by deptno;
    select deptno, sum(sum(sal)) sumsal
    *
    ERROR at line 1:
    ORA-00937: not a single-group group function

    Even if it were not illegal, what would "sum of sum of sal" by deptno actually mean?

  6. #6
    Join Date
    Jan 2004
    Posts
    4

    Talking

    Thank you! The " perhaps you want to SUM to one level and STDDEV to a higher level (but not just a single result)? Something like" was exactly what I was looking for. Thanks again!!!!

Posting Permissions

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