Oracle SQL-Error: ORA-00937: not a single-group group function
Tag : sql , By : user187301
Date : March 29 2020, 07:55 AM
it should still fix some issue your question isn't very clear.. what tables are the fields from? you will need to have sub queries for your 3 fields, since they all require group by clauses create or replace view DeptInfo as
SELECT q1.dnumber,
q1.dname,
q1.AvgSal,
q2.MaxSal,
q3.MinSal
FROM (SELECT dnumber, dname, AVG(salary) as AvgSal
FROM department, employee
GROUP BY dnumber, dname) q1,
(SELECT dnumber, dname, MAX(salary) as MaxSal
FROM department, employee
GROUP BY dnumber, dname) q2,
(SELECT dnumber, dname, MIN(salary) as MinSal
FROM department, employee
GROUP BY dnumber, dname) q3
WHERE q1.dnumber = q2.dnumber AND
q2.dnumber = q3.dnumber AND
q1.dname = q2.dname AND
q2.dname = q3.name
|
why oracle user defined function shows error in Oracle Sql Developer 4.0.3.16
Date : March 29 2020, 07:55 AM
wish help you to fix your issue In Oracle SQL Developer Version 1.5.4, same code doesn't give any error. I am currently connected to oracle 11g database server. , v_datevalue date;
begin
v_datevalue := '01-APR-2015';
SQL> ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY';
Session altered.
SQL>
SQL> CREATE OR REPLACE
2 FUNCTION GETDATE
3 RETURN VARCHAR2
4 IS
5 v_datevalue DATE;
6 BEGIN
7 v_datevalue := '01-APR-2015';
8 RETURN (CAST(v_datevalue AS VARCHAR2));
9 END;
10 /
Function created.
SQL>
SQL> SELECT GETDATE FROM DUAL;
SELECT GETDATE FROM DUAL
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "LALIT.GETDATE", line 6
SQL>
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';
Session altered.
SQL>
SQL> SELECT GETDATE FROM DUAL;
GETDATE
-----------------------------------------------------
01-APR-2015
SQL>
SQL> ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY';
Session altered.
SQL>
SQL> CREATE OR REPLACE
2 FUNCTION GETDATE
3 RETURN VARCHAR2
4 IS
5 v_datevalue DATE;
6 BEGIN
7 v_datevalue := TO_DATE('01-APR-2015','DD-MON-YYYY');
8 RETURN (CAST(v_datevalue AS VARCHAR2));
9 END;
10 /
Function created.
SQL>
SQL> SELECT GETDATE FROM DUAL;
GETDATE
-------------------------------------------------------------
04/01/2015
SQL>
|
Oracle : Error ORA-00937: not a single-group group function in Oracle Join Query
Date : March 29 2020, 07:55 AM
I wish this help you I suppose you have to add cy.continent to the select statement of your first try when you group by Cy.continent. Try something like that (floor is for the "round it down to the nearest integer" - part): SELECT floor(AVG(City.population)),Country.continent FROM City JOIN Country ON City.CountryCode = Country.Code
GROUP BY Country.continent;
|
Error when query Oracle: not a single-group group function
Date : March 29 2020, 07:55 AM
|
ORACLE SINGLE GROUP FUNCTION ERROR WITH SUBQUERY
Tag : sql , By : Juan Pablo
Date : March 29 2020, 07:55 AM
will be helpful for those in need I'd avoid subqueries in SELECT statement; they rarely do something good, might cause bad performance and can - usually - be rewritten as select count(*), t2.email
from table1 t1 join table2 t2 on t2.id = t.1.id
group by t2.email
|
Boards Message :
Related QUESTIONS :