Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00937: not a single-group group function
ORA-00937: not a single-group group function [message #228478] Tue, 03 April 2007 03:31 Go to next message
AbuShreek
Messages: 22
Registered: May 2006
Junior Member
Hello,

The following query is giving me the above error:

SELECT   MAX (SUM(COL1)/AVG(COL2)) AS MAX_TRAFFIC,
         PERIOD_TIME
FROM     TRAFFIC
WHERE    STREET= 'STREET123'
         AND TO_CHAR (START_TIME, 'DD-MM-YYYY') = '02-04-2007'
GROUP BY STREET, START_TIME;


It is not accepting the START_TIME column although it is listed in the GROUP BY columns.

Any idea? Thanks in advance

Re: ORA-00937: not a single-group group function [message #228480 is a reply to message #228478] Tue, 03 April 2007 03:35 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
PERIOD_TIME is not in your GROUP BY clause.

MHE
Re: ORA-00937: not a single-group group function [message #228567 is a reply to message #228478] Tue, 03 April 2007 06:51 Go to previous messageGo to next message
AbuShreek
Messages: 22
Registered: May 2006
Junior Member
I am sorry, I mistyped the query. There is no PERIOD_TIME column. Here it is again:

SELECT   MAX (SUM(COL1)/AVG(COL2)) AS MAX_TRAFFIC,
         START_TIME
FROM     TRAFFIC
WHERE    STREET= 'STREET123'
         AND TO_CHAR (START_TIME, 'DD-MM-YYYY') = '02-04-2007'
GROUP BY STREET, START_TIME;
Re: ORA-00937: not a single-group group function [message #228587 is a reply to message #228567] Tue, 03 April 2007 07:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Odd- I'd have thought that would work.
Seems Oracle is objecting to the MAX operating on the other aggregate functions.
Your query will work if rewritten as an inline view:
SELECT   start_time
        ,MAX(traffic)
FROM    (SELECT   SUM(COL1)/AVG(COL2) AS TRAFFIC
                 ,START_TIME
         FROM     TRAFFIC
         WHERE    STREET= 'STREET123'
         AND      TO_CHAR (START_TIME, 'DD-MM-YYYY') = '02-04-2007'
         GROUP BY STREET, START_TIME)
GROUP BY start_time;
Re: ORA-00937: not a single-group group function [message #228996 is a reply to message #228567] Wed, 04 April 2007 22:34 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
AbuShreek wrote on Tue, 03 April 2007 06:51
I am sorry, I mistyped the query. There is no PERIOD_TIME column. Here it is again:

SELECT   MAX (SUM(COL1)/AVG(COL2)) AS MAX_TRAFFIC,
         START_TIME
FROM     TRAFFIC
WHERE    STREET= 'STREET123'
         AND TO_CHAR (START_TIME, 'DD-MM-YYYY') = '02-04-2007'
GROUP BY STREET, START_TIME;



street is not in select clause but in group by clause
Re: ORA-00937: not a single-group group function [message #229011 is a reply to message #228996] Thu, 05 April 2007 00:55 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
muzahidul islam wrote on Thu, 05 April 2007 05:34
AbuShreek wrote on Tue, 03 April 2007 06:51
I am sorry, I mistyped the query. There is no PERIOD_TIME column. Here it is again:

SELECT   MAX (SUM(COL1)/AVG(COL2)) AS MAX_TRAFFIC,
         START_TIME
FROM     TRAFFIC
WHERE    STREET= 'STREET123'
         AND TO_CHAR (START_TIME, 'DD-MM-YYYY') = '02-04-2007'
GROUP BY STREET, START_TIME;



street is not in select clause but in group by clause

So?
SQL> desc cat
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- ------------
 TABLE_NAME                                                                          NOT NULL VARCHAR2(30)
 TABLE_TYPE                                                                                   VARCHAR2(11)

SQL> select count(*) from cat group by table_type;

  COUNT(*)
----------
       739
      1678
       672
       971
Previous Topic: Capture dml statements fired on a database
Next Topic: tabular query
Goto Forum:
  


Current Time: Sat Dec 03 18:22:14 CST 2016

Total time taken to generate the page: 0.22637 seconds