Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    38

    Unanswered: ORA-00937: not a single-group group

    SELECT Q_PROT.name,Q_KT.name,
    SUM(CASE WHEN Q_DPP.amount=MAX (Q_DPP.amount) THEN amount ELSE 0 END)*1.0/SUM(CASE WHEN Q_DPP.amount=MAX (Q_DPP.amount) THEN 1 ELSE 0 END)AS MAX,
    SUM(CASE WHEN Q_DPP.amount=MIN (Q_DPP.amount) THEN ??S? ELSE 0 END)*1.0/SUM(CASE WHEN Q_DPP.amount=MIN (Q_DPP.amount) THEN 1 ELSE 0 END)AS MIN
    FROM Q_PROT,Q_ERGO,Q_DPP,Q_KT
    WHERE ((Q_ERGO.cod_p=Q_PROT.cod_p) AND (Q_ERGO.cod_e=Q_DPP.cod_e) AND (Q_KT.cod_d=Q_DPP.cod_d))
    GROUP BY Q_PROT.name,Q_KT.name,Q_DPP.amount

    i did that but have this mistake
    ORA-00937: not a single-group group function. some help pls

    i dont post data sample because there are 5 tables connected, but if you need to see the tables that's no problem.

    thank you

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Code:
    SUM(CASE WHEN Q_DPP.amount=MAX (Q_DPP.amount) THEN amount ELSE 0 END)
    Just curious what you want to achieve by this. Of course it is incorrect as MAX does not have any GROUP BY clause. Even if it would be empty (= overall MAX), it is syntactically ambiguous to which aggregate does the GROUP BY clause belong.
    You shall either put counting MAX into subquery or use MAX in analytic form.
    i dont post data sample because there are 5 tables connected, but if you need to see the tables that's no problem.
    If you would spend a time to create a testcase, you would not need all that tables; you could put all relevant columns into one table (created only for the demonstration purpose).

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's gotta be my mostest favourite oracle error message evar

    you have MAX() inside SUM(), but you can't nest aggregate functions like that

    what are you trying to achieve?

    Edit: looks like i spent too long trying to understand what you were trying to achieve, somebody else posted ahead of me -- sorry, i wasn't echoing, at least not knowingly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2009
    Posts
    38
    Q_DPP TABLE
    cod_de, cod_e, cod_d, date, amount
    1, 1, 1, 01/11/2007, 20000
    2, 1, 3, 03/10/2007, 6000
    3, 1, 6, 04/08/2007, 1000
    4, 1, 1, 09/10/2008, 8000
    5, 1, 3, 03/06/2008, 4500
    6, 1, 6, 23/07/2008, 1000
    7, 1, 2, 22/01/2008 ,1000
    8, 2, 3, 11/12/2007 ,3000
    9, 2, 6, 12/01/2008 ,1500
    10, 2, 3, 09/03/2008 ,2500
    11, 2, 6, 05/07/2007 ,1500
    12, 2, 5, 04/08/2007 ,2000
    13, 3, 2, 01/02/2007 ,100000
    14, 3, 4, 09/03/2007 ,200000
    15, 3, 5, 22/06/2008 ,250000
    16, 3, 2, 14/04/2008 100000
    17, 3, 4, 28/05/2007 100000
    18, 3, 5, 15/05/2008 200000
    19, 3, 6, 01/12/2007 10000

    Q_KT TABLE

    COD_D, NAME
    1, A
    2, B
    3, C
    4, D
    5, E
    6, F

    Q_PROT TABLE
    COD_P,CODE_EP,NAME
    1, 1,G
    2, 1,H
    3, 1, I
    4, 1, K
    5, 1, L
    6, 1, M
    1, 2, N
    2, 2, O
    3, 2, P

    Q_ERGO TABLE

    COD_E,COD_M,COD_P,COD_EP
    1 , 1, 1, 2,
    2, 2, 4, 1,
    3, 2, 2, 1


    I must find for every Q_PROT (name), the Q_KT(NAME) with the largest sum amount of Q_DPP and with the minimum sum amount from DPP.

    eg

    28000,g ,a (maximum for g)
    1000 ,g,b (minimum for g)

    450000,k,c (maximum for k)
    10000,k,d(minimum for k)

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by flyboy
    If you would spend a time to create a testcase, you would not need all that tables; you could put all relevant columns into one table (created only for the demonstration purpose).
    I do not have time to create a testcase (CREATE TABLE + INSERT statements to easily put it into database to play with). You can simplify that problem if current data is unnecessarily large (which is this case); at least you will get the core of the problem as joins do not affect it.
    If you provided a testcase, I would demonstrate the usage of MAX function in analytic form (I already did it, but on tables I do not want to post here).
    But maybe someone else will do it without testing or creating the tables with data himself or using similar data structures...

    Instead, you may study the usage of MAX analytic function yourself.

  6. #6
    Join Date
    Feb 2009
    Posts
    38
    if i do this i get :

    SELECT SUM (Q_DPP.amount),Q_PROT.name,Q_KT.name
    FROM Q_PROT,Q_DPP,Q_KT,Q_ERGO
    WHERE ((Q_ERGO.cod_p=Q_PROT.cod_p) AND (Q_KT.cod_d=Q_DPP.cod_d) AND (Q_ERGO.cod_e=Q_DPP.cod_e))
    GROUP BY Q_PROT.name,Q_KT.name
    ORDER BY Q_PROT.name

    and i take

    sum, q_prot.name, q_kt.name

    28000,development ,nick
    2000 ,development,maria
    1000 ,development,merc
    10500,development,john
    10000,magic,lea
    200000,magic,maraia
    450000,magic,george
    300000,magic,jim

    instead of that i need only the max and the min amount for the second

    name(Q_PROT.name).
    i mean i must take

    28000,development ,nick
    1000 ,development,merc
    450000,magic,george
    10000,magic,lea

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by r937
    you have MAX() inside SUM(), but you can't nest aggregate functions like that
    Rudy, while the OP's query is definitely wrong, it appears that in Oracle you can nest aggregates, sometimes at least:

    Code:
    SQL> -- Some SQL Plus reporting commands...
    SQL> compute sum of maxsal on report
    SQL> break on report
    SQL> select deptno, max(sal) as maxsal from emp group by deptno;
    
        DEPTNO     MAXSAL
    ---------- ----------
            17
            30      20000
            99       5800
            98      10000
            40      99999
            20       8000
            10      10000
               ----------
    sum            153799
    
    7 rows selected.
    
    SQL> select sum(max(sal)) from emp group by deptno;
    
    SUM(MAX(SAL))
    -------------
           153799
    I have no idea whether this is legitimate or a bug - what happens on other DBMSs if you try that?

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by andrewst
    I have no idea whether this is legitimate or a bug - what happens on other DBMSs if you try that?
    As long as you do not include any columns in the SELECT clause (so the GROUP BY clause belongs to the inner aggregate), it may make sense - the second aggregate returns one overall value for all table.

    Just update to my previous post: even using analytic MAX function requires using it in a subquery; in that case using it makes no benefit. So try
    put counting MAX into subquery
    However, when looking at the results, you may use analytic MAX on that intermediate resultset.

  9. #9
    Join Date
    Feb 2009
    Posts
    38
    ok partition row and problem solved. thanks

Posting Permissions

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