당신은 주제를 찾고 있습니까 “ora 00934 group function is not allowed here – Oracle SQL Throwing \”ORA-00934: group function is not allowed here\” error for simple Select w/AVG“? 다음 카테고리의 웹사이트 Chewathai27.com/you 에서 귀하의 모든 질문에 답변해 드립니다: Chewathai27.com/you/blog. 바로 아래에서 답을 찾을 수 있습니다. 작성자 Roel Van de Paar 이(가) 작성한 기사에는 조회수 56회 및 좋아요 1개 개의 좋아요가 있습니다.
Cause: One of the group functions, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, was used in a WHERE or GROUP BY clause. Action: Remove the group function from the WHERE or GROUP BY clause.To resolve the error, you can either remove the group function or column expression from the SELECT clause or you can add a GROUP BY clause that includes the column expressions.The MIN function can be used only with numeric data. The MAX function can be used only with date values. The AVG function can be used only with numeric data.
ora 00934 group function is not allowed here 주제에 대한 동영상 보기
여기에서 이 주제에 대한 비디오를 시청하십시오. 주의 깊게 살펴보고 읽고 있는 내용에 대한 피드백을 제공하세요!
d여기에서 Oracle SQL Throwing \”ORA-00934: group function is not allowed here\” error for simple Select w/AVG – ora 00934 group function is not allowed here 주제에 대한 세부정보를 참조하세요
Oracle SQL Throwing \”ORA-00934: group function is not allowed here\” error for simple Select w/AVG
Helpful? Please support me on Patreon: https://www.patreon.com/roelvandepaar
With thanks \u0026 praise to God, and with thanks to the many people who have made this project possible! | Content (except music \u0026 images) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing | Music: https://www.bensound.com/licensing | Images: https://stocksnap.io/license \u0026 others | With thanks to user codesquatch (dba.stackexchange.com/users/77949), user Balazs Papp (dba.stackexchange.com/users/28019), and the Stack Exchange Network (dba.stackexchange.com/questions/120427). Trademarks are property of their respective owners. Disclaimer: All information is provided \”AS IS\” without warranty of any kind. You are responsible for your own actions. Please contact me if anything is amiss at Roel D.OT VandePaar A.T gmail.com
ora 00934 group function is not allowed here 주제에 대한 자세한 내용은 여기를 참조하세요.
Group function is not allowed here – Stack Overflow
When I run the following query, I get. ORA-00934: group function is not allowed here.
Source: stackoverflow.com
Date Published: 1/11/2022
View: 9183
Oracle / PLSQL: ORA-00934 Error Message – TechOnTheNet
When you encounter an ORA-00934 error, the following error message will appear: ORA-00934: group function is not allowed here. Cause. You tried to execute a SQL …
Source: www.techonthenet.com
Date Published: 11/30/2022
View: 8618
ORA-00934: group function is not allowed here – IT Tutorial
This ORA-00934 error is related to the group function which is not allowed. You need to use HAVING CLAUSE For filtering if you use Aggregate …
Source: ittutorial.org
Date Published: 8/12/2022
View: 157
ORA-00934: group function is not allowed here
Solution: Remove the group function from the WHERE or GROUP BY clause. The desired result may be achieved by including the function in a subquery or HAVING …
Source: www.plsql.co
Date Published: 8/25/2021
View: 4468
ORA-00934: group function is not allowed here — oracle-tech
Hi all, Good Morning. My db version is 12.2. I am getting below errors. I appreciate if someone help out this querry SQL> ed Wrote file …
Source: community.oracle.com
Date Published: 10/19/2021
View: 5196
Oracle SQL Throwing “ORA-00934: group function is not …
Here is what I thought would work. Any help appreciated. SELECT COL1, COL2 FROM TABLE1 HAVING COL3 > 0 AND COL3 …
Source: dba.stackexchange.com
Date Published: 3/15/2021
View: 4097
ORA-00934: Group feature is not allowed here – eehelp.com
You have deleted the WITH clause. The parser can recognize the error when it has reached the …
Source: www.eehelp.com
Date Published: 9/18/2022
View: 4795
ORA – 00934: Group function is not allowed here – whereisstuff
ORA – 00934: Group function is not allowed here … Solution: v Instead of where clause, we should use HAVING CLAUSE For filtering. v Because SQL …
Source: www.whereisstuff.com
Date Published: 3/20/2021
View: 4469
Sql – Group function is not allowed here – iTecNote
… is not allowed here. oraclesqlsqlplus … ORA-00934: group function is not allowed here … You cannot use an aggregate function in a WHERE clause.
Source: itecnote.com
Date Published: 8/20/2022
View: 4858
주제와 관련된 이미지 ora 00934 group function is not allowed here
주제와 관련된 더 많은 사진을 참조하십시오 Oracle SQL Throwing \”ORA-00934: group function is not allowed here\” error for simple Select w/AVG. 댓글에서 더 많은 관련 이미지를 보거나 필요한 경우 더 많은 관련 기사를 볼 수 있습니다.
주제에 대한 기사 평가 ora 00934 group function is not allowed here
- Author: Roel Van de Paar
- Views: 조회수 56회
- Likes: 좋아요 1개
- Date Published: 2021. 10. 6.
- Video Url link: https://www.youtube.com/watch?v=DzXi5Yv5ufI
How do I fix Ora 00937 Not a single group group function?
To resolve the error, you can either remove the group function or column expression from the SELECT clause or you can add a GROUP BY clause that includes the column expressions.
What group function is not allowed with DATE datatype?
The MIN function can be used only with numeric data. The MAX function can be used only with date values. The AVG function can be used only with numeric data.
What is not a single group group function?
Oracle’s error message is “ORA-00937: not a single-group group function”. Its meaning is “A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.”
Is not a GROUP BY expression?
ORA-00979 “ Not a GROUP BY expression ” is an error issued by the Oracle database when the SELECT statement contains a column that is neither listed in GROUP BY nor aggregated. This error message can be confusing to beginners. Practice your SQL knowledge with our SQL Practice Set course.
What is the use of GROUP BY in SQL?
The SQL GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary rows, like “find the number of customers in each country”. The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
What is missing expression in SQL?
All that ‘missing expression’ means is that When attempting to operate a query, a particular part of the clause necessary for it to function was omitted in the text. Stated simply, you left out an important chunk of what you were trying to run.
What are Oracle group functions?
In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
What are group functions?
Group functions are mathematical functions to operate on sets of rows to give one result per set. The types of group functions (also called aggregate functions) are: AVG, that calculates the average of the specified columns in a set of rows, COUNT, calculating the number of rows in a set.
How do you use group functions?
…
Example of How to Group in Excel
- Select the rows you wish to add grouping to (entire rows, not just individual cells)
- Go to the Data Ribbon.
- Select Group.
- Select Group again.
What is invalid identifier in Oracle?
Ora-00904 Error Message “Invalid Identifier”
This error is most common when querying a SELECT statement. To resolve this error, first check to make sure the column name being referenced exists. If it does not exist, you must create one before attempting to execute an SQL statement with the column.
What is missing NULL keyword in SQL?
ORA-00908: missing NULL keyword
Cause: Either of the following: In a CREATE TABLE or ALTER TABLE statement, NOT was entered to specify that no null values are allowed in that column, but the keyword NULL was omitted.
What is missing left parenthesis in Oracle?
ORA-00906: missing left parenthesis error occurs when the left parenthesis is missing in SQL statements such as create table, insert, select, subquery, and IN clause. SQL statements that include multiple items should be contained in parentheses.
What is Ora 00979 Not A GROUP BY expression?
ORA-00979 occurs when the GROUP BY clause does not contain all the expressions in the SELECT clause. Any SELECT expression that is not included in the GROUP function must be listed in the GROUP BY clause. These are AVG, COUNT, MAX, MIN, SUM, STDDEV, and VARIANCE.
What is GROUP BY clause in Oracle?
A GROUP BY clause, part of a SelectExpression, groups a result into subsets that have matching values for one or more columns. In each group, no two rows have the same value for the grouping column or columns. NULLs are considered equivalent for grouping purposes.
How aggregate function works in SQL?
An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT(*) , aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement. All aggregate functions are deterministic.
How do I fix Ora 01489 result of string concatenation is too long?
- Change OBJECT_NAME to the column you wish to comma-delimit.
- Modify OBJECT_ID to the expression you want to sort on.
- Also you’ll have to change ‘, ‘ to whatever you want to delimit your list with.
What is the purpose of the group functions in SQL give some examples of group functions?
Group functions are mathematical functions to operate on sets of rows to give one result per set. The types of group functions (also called aggregate functions) are: AVG, that calculates the average of the specified columns in a set of rows, COUNT, calculating the number of rows in a set.
What is GROUP BY clause in SQL?
In SQL, the GROUP BY clause is used to group rows by one or more columns. For example, SELECT country, COUNT(*) AS number FROM Customers GROUP BY country; Run Code. Here, the SQL command groups the rows by the country column, and counts the number of each country (because of the COUNT() function).
What does missing right parenthesis mean in SQL?
This error indicates that there is a left parenthesis but no corresponding right parenthesis, or that additional information was contained within the parentheses.
Server Messages: ORA-00000 to ORA-02350
00900-00999: SQL Parsing Messages
This section lists some of the messages generated when SQL statements are parsed by the Oracle Server. Most, but not all, messages in this section indicate incorrect SQL syntax. For SQL syntax, refer to Oracle8 Server SQL Reference.
If you are using Trusted Oracle, see the Trusted Oracle documentation for information about error messages in that environment.
ORA-00900: invalid SQL statement
Cause: The statement is not recognized as a valid SQL statement. This error can occur if the Procedural Option is not installed and a SQL statement is issued that requires this option; for example, a CREATE PROCEDURE statement. You can determine if the Procedural Option is installed by starting SQL*Plus. If the PL/SQL banner is not displayed, then the option is not installed.
Action: Correct the syntax or install the Procedural Option.
ORA-00901: invalid CREATE command
Cause: The CREATE command was not followed by a valid CREATE option.
Action: Correct the syntax.
ORA-00902: invalid datatype
Cause: The datatype entered in the CREATE or ALTER TABLE statement is not valid.
Action: Correct the syntax.
ORA-00903: invalid table name
Cause: A table or cluster name is invalid or does not exist. This message is also issued if an invalid cluster name or no cluster name is specified in an ALTER CLUSTER or DROP CLUSTER statement.
Action: Check spelling. A valid table name or cluster name must begin with a letter and may contain only alphanumeric characters and the special characters $, _, and #. The name must be less than or equal to 30 characters and cannot be a reserved word.
ORA-00904: invalid column name
Cause: The column name entered is either missing or invalid.
Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, it must be enclosed in double quotation marks. It may not be a reserved word.
ORA-00905: missing keyword
Cause: A required keyword is missing.
Action: Correct the syntax.
ORA-00906: missing left parenthesis
Cause: A required left parenthesis has been omitted. Certain commands, such as CREATE TABLE, CREATE CLUSTER, and INSERT, require a list of items enclosed in parentheses. Parentheses also are required around subqueries in WHERE clauses and in UPDATE table SET column = (SELECT…) statements.
Action: Correct the syntax, inserting a left parenthesis where required, and retry the statement.
ORA-00907: missing right parenthesis
Cause: A left parenthesis has been entered without a closing right parenthesis, or extra information was contained in the parentheses. All parentheses must be entered in pairs.
Action: Correct the syntax and retry the statement.
ORA-00908: missing NULL keyword
Cause: Either of the following: In a CREATE TABLE or ALTER TABLE statement, NOT was entered to specify that no null values are allowed in that column, but the keyword NULL was omitted. In the IS [NOT] NULL logical operator, the keyword NULL was not found. For example, the following statement generates this message:
SELECT * FROM EMP WHERE DEPTNO IS NOT; The keyword NULL must follow the keywords IS NOT.
Action: Correct the syntax.
ORA-00909: invalid number of arguments
Cause: An Oracle function was referenced with an incorrect number of arguments. All Oracle functions, except for SYSDATE, require at least one argument.
Action: Correct the syntax of the function by entering the required number of arguments.
ORA-00910: specified length too long for its datatype
Cause: No size was specified for a character field or the size was invalid. A maximum length must be specified for each character column. The maximum value for this length varies for each character datatype, such as CHAR or VARCHAR2.
Action: Enter a maximum length for the field.
ORA-00911: invalid character
Cause: Special characters are valid only in certain places. If special characters other than $, _, and # are used in a name and the name is not enclosed in double quotation marks (“), this message will be issued. One exception to this rule is for database names; in this case, double quotes are stripped out and ignored.
Action: Remove the invalid character from the statement or enclose the object name in double quotation marks.
ORA-00913: too many values
Cause: The SQL statement requires two sets of values equal in number. This error occurs when the second set contains more items than the first set. For example, the subquery in a WHERE or HAVING clause may return too many columns, or a VALUES or SELECT clause may return more columns than are listed in the INSERT.
Action: Check the number of items in each set and change the SQL statement to make them equal.
ORA-00914: missing ADD keyword
Cause: The keyword ADD does not precede one of the following:
the keyword log file in an ALTER DATABASE statement
a column element or table constraint in an ALTER TABLE statement
the keyword DATAFILE in an ALTER TABLESPACE statement
Action: Specify the keyword ADD in the ALTER statement.
ORA-00915: network access of dictionary table not currently allowed
Cause: This is an internal error message not usually issued.
Action: Contact customer support.
ORA-00917: missing comma
Cause: A required comma has been omitted from a list of columns or values in an INSERT statement or a list of the form ((C,D),(E,F), …).
Action: Correct the syntax.
ORA-00918: column ambiguously defined
Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.
Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.
ORA-00919: invalid function
Cause: An entry was formatted like a function call, but it is not recognizable as an Oracle function.
Action: Correct the syntax.
ORA-00920: invalid relational operator
Cause: A search condition was entered with an invalid or missing relational operator.
Action: Include a valid relational operator such as =, !=, ^=, <>, >, <, >=, <=, ALL, ANY, [NOT] BETWEEN, EXISTS, [NOT] IN, IS [NOT] NULL, or [NOT] LIKE in the condition. ORA-00921: unexpected end of SQL command Cause: The SQL command was not complete. Part of a valid command was entered, but at least one major component was omitted. Action: Correct the syntax. ORA-00922: missing or invalid option Cause: An invalid option was specified in defining a column or storage clause. The valid option in specifying a column is NOT NULL to specify that the column cannot contain any NULL values. Only constraints may follow the datatype. Specifying a maximum length on a DATE or LONG datatype also causes this error. Action: Correct the syntax. Remove the erroneous option or length specification from the column or storage specification. ORA-00923: FROM keyword not found where expected Cause: In a SELECT or REVOKE statement, the keyword FROM was either missing, misplaced, or misspelled. The keyword FROM must follow the last selected item in a SELECT statement or the privileges in a REVOKE statement. Action: Correct the syntax. Insert the keyword FROM where appropriate. The SELECT list itself also may be in error. If quotation marks were used in an alias, check that double quotation marks enclose the alias. Also, check to see if a reserved word was used as an alias. ORA-00924: missing BY keyword Cause: The keyword BY was omitted in a GROUP BY, ORDER BY, or CONNECT BY clause. In a GRANT statement, the keyword IDENTIFIED must also be followed by the keyword BY. Action: Correct the syntax. Insert the keyword BY where required and then retry the statement. ORA-00925: missing INTO keyword Cause: An INSERT statement has been entered without the keyword INTO. Action: Correct the syntax. Insert the keyword INTO where required and then retry the statement. ORA-00926: missing VALUES keyword Cause: An INSERT statement has been entered without the keyword VALUES or SELECT. Either a VALUES clause or a SELECT subquery must follow the INSERT INTO clause. Action: Correct the syntax. Enter either a VALUES clause or a subquery after the INSERT INTO clause. ORA-00927: missing equal sign Cause: An equal sign has been omitted in one of the following places: in the SET clause of an UPDATE statement following "!" in a search condition to signify not equal Action: Correct the syntax. Insert the equal sign where required and retry the statement. ORA-00928: missing SELECT keyword Cause: A SELECT subquery must be included in a CREATE VIEW statement. Action: Correct the syntax. Insert the required SELECT clause after the CREATE VIEW clause and then retry the statement. ORA-00929: missing period Cause: This is an internal error message not usually issued. Action: Contact customer support. ORA-00930: missing asterisk Cause: This is an internal error message not usually issued. Action: Contact customer support. ORA-00931: missing identifier Cause: This is an internal error message not usually issued. Action: Contact customer support. ORA-00932: inconsistent datatypes Cause: Either An attempt was made to perform an operation on incompatible datatypes. For example, adding a character field to a date field (dates may only be added to numeric fields) or concatenating a character field with a long field. An attempt was made to perform an operation on a database object (such as a table or view) that is not intended for normal use. For example, system tables cannot be modified by a user. Note that on rare occasions this error occurs because a misspelled object name matched a restricted object's name. An attempt was made to use an undocumented view. Action: If the cause is different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields. an object not intended for normal use, then do not access the restricted object. ORA-00933: SQL command not properly ended Cause: The SQL statement ends with an inappropriate clause. For example, an ORDER BY clause may have been included in a CREATE VIEW or INSERT statement. ORDER BY cannot be used to create an ordered view or to insert in a certain order. Also, an improper SQL ending occurs if IN clause is used with only one argument (IN(X), for example). An IN clause must have two or more arguments. Action: Correct the syntax by removing the inappropriate clauses. It may be possible to duplicate the removed clause with another SQL statement. For example, to order the rows of a view, do so when querying the view and not when creating it. This error can also occur in SQL*Forms applications if a continuation line is indented. Check for indented lines and delete these spaces. ORA-00934: group function is not allowed here Cause: One of the group functions, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, was used in a WHERE or GROUP BY clause. Action: Remove the group function from the WHERE or GROUP BY clause. The desired result may be achieved by including the function in a subquery or HAVING clause. ORA-00935: group function is nested too deeply Cause: This is an internal error message not usually issued. Action: Contact customer support. ORA-00936: missing expression Cause: A required part of a clause or expression has been omitted. For example, a SELECT statement may have been entered without a list of columns or expressions or with an incomplete expression. This message is also issued in cases where a reserved word is misused, as in SELECT TABLE. Action: Check the statement syntax and specify the missing component. ORA-00937: not a single-group group function Cause: A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause. Action: Drop either the group function or the individual column expression from the SELECT list or add a GROUP BY clause that includes all individual column expressions listed. ORA-00938: not enough arguments for function Cause: The function was referenced with too few arguments. Action: Check the function syntax and specify the required number of arguments. ORA-00939: too many arguments for function Cause: The function was referenced with too many arguments. Action: Check the function syntax and specify only the required number of arguments. ORA-00940: invalid ALTER command Cause: An invalid ALTER option was specified. Action: Correct the syntax. ORA-00941: missing cluster name Cause: The cluster name was either missing or invalid. Action: Specify a valid cluster name. A valid cluster name must start with a letter, be less than or equal to 30 characters, and contain only alphanumeric characters or the special characters $, _, and #. It may not be a reserved word. The name must be specified immediately following the keywords CREATE CLUSTER. ORA-00942: table or view does not exist Cause: The table or view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. Existing user tables and views can be listed by querying the data dictionary. Certain privileges may be required to access the table. If an application returned this message, the table the application tried to access does not exist in the database, or the application does not have access to it. Action: Check each of the following: the spelling of the table or view name. that a view is not specified where a table is required. that an existing table or view name exists. Contact the database administrator if the table needs to be created or if user or application privileges are required to access the table. Also, if attempting to access a table or view in another schema, make certain the correct schema is referenced and that access to the object is granted. ORA-00943: cluster does not exist Cause: The current user owns no cluster by the specified name. Action: Specify a valid cluster name following the keyword CLUSTER, then retry the statement. ORA-00944: insufficient number of clustered columns Cause: An attempt was made to create a table with fewer cluster columns than were specified in the CREATE CLUSTER statement. The CLUSTER clause of a CREATE TABLE statement must specify all cluster columns that were defined when the cluster was created. Action: Specify all cluster columns in the CREATE TABLE statement, then retry it. ORA-00945: specified clustered column does not exist Cause: A column specified in the cluster clause of a CREATE TABLE statement is not a column in this table. Action: Re-execute the statement, using the names of columns defined for the table. ORA-00946: missing TO keyword Cause: A GRANT statement was specified without the keyword TO, or an invalid form of the GRANT command was entered. Action: Check the syntax for the GRANT command, insert the keyword TO where required, and retry the statement. ORA-00947: not enough values Cause: This error occurs when a SQL statement requires two sets of values equal in number, but the second set contains fewer items than the first set. This can occur in a WHERE or HAVING clause in which a nested SELECT returns too few columns as in: WHERE (A,B) IN (SELECT C FROM ...) Another common cause of this error is an INSERT statement in which the VALUES or SELECT clause does not contain enough values needed for the INSERT, as in INSERT INTO EMP(EMPNO,ENAME) VALUES('JONES') Action: Check the number of items in each set and change the SQL statement to make them equal. ORA-00948: ALTER CLUSTER statement no longer supported Cause: The ALTER CLUSTER statement has been withdrawn. Action: To add data to a cluster from an existing table, use the following series of SQL statements: CREATE TABLE newtable SELECT * FROM oldtable CLUSTER clustername; DROP oldtable; RENAME TABLE newtable oldtable; ORA-00949: illegal reference to remote database Cause: This is an internal error message not usually issued. Action: Contact customer support. ORA-00950: invalid DROP option Cause: A DROP command was not followed by a valid DROP option, such as CLUSTER, DATABASE LINK, INDEX, ROLLBACK SEGMENT, SEQUENCE, SYNONYM, TABLE, TABLESPACE, or VIEW. Action: Check the command syntax, specify a valid DROP option, then retry the statement. ORA-00951: cluster not empty Cause: A DROP CLUSTER statement specified a cluster that is not empty. A cluster may not be dropped if it contains any tables, unless the optional INCLUDING TABLES clause is specified. Tables may also be removed from a cluster by using the DROP TABLE command. Action: Either specify the INCLUDING TABLES clause in the DROP CLUSTER statement or remove all tables from the cluster with the DROP TABLE command before issuing the DROP CLUSTER command. ORA-00952: missing GROUP keyword Cause: Groups are not currently implemented. Action: No user action required. ORA-00953: invalid index name Cause: In a CREATE INDEX, DROP INDEX, or VALIDATE INDEX statement, the index name was missing or invalid. Action: Specify a valid index name after the keyword INDEX. To drop or validate an existing index, check the name by querying the data dictionary. To create a new index, check the syntax before retrying. ORA-00954: missing IDENTIFIED keyword Cause: A GRANT CONNECT statement was issued without the keyword IDENTIFIED. Action: Check the syntax and insert the keyword IDENTIFIED after the last username. The format is GRANT CONNECT TO user-list IDENTIFIED BY password-list; ORA-00955: name is already used by an existing object Cause: An attempt was made to create a database object (such as a table, view, cluster, index, or synonym) that already exists. A user's database objects must have distinct names. Action: Enter a unique name for the database object or modify or drop the existing object so it can be reused. ORA-00956: missing or invalid auditing option Cause: An AUDIT or NOAUDIT command was not followed by a valid option or the keyword ALL. For example, when AUDITing tables an option such as ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, or UPDATE must be specified. Action: Correct the syntax. ORA-00957: duplicate column name Cause: A column name was specified twice in a CREATE or INSERT statement. Column names must be unique within a table, view, or cluster. Action: In a CREATE statement, change one of the column names to a new, unique column name. In an INSERT statement, remove one of the duplicate names. ORA-00958: missing CHECK keyword Cause: The keyword CHECK should follow the keyword WITH in the WITH OPTION clause of the CREATE VIEW statement. Action: Check the statement syntax and insert the keyword CHECK where required. Then retry the statement. ORA-00959: tablespace "name" does not exist Cause: A statement specified the name of a tablespace that does not exist. Action: Enter the name of an existing tablespace. For a list of tablespace names, query the data dictionary. If a tablespace is dropped and re-created with the same name, use ALTER USER to reset the default or temporary tablespace name, because the new tablespace is not the same as the dropped tablespace, even though they have the same name. ORA-00960: ambiguous column naming in select list Cause: A column name in the order-by list matches more than one select list column. Action: Remove the duplicate column naming in the select list. ORA-00962: too many group-by or order-by expressions Cause: The group-by or order-by column list contains more than 255 expressions. Action: Use no more than 255 expressions in the group-by or order-by list. ORA-00964: table name not in FROM list Cause: A table specified in a query's SELECT list is not named in the FROM clause list. Action: Check spelling of the table names, check that each table name in the SELECT list matches a table name in the FROM list, and then retry the statement. ORA-00965: column aliases not allowed for "*" Cause: An alias was used with the return-all-columns function (*) in the SELECT list. For example: SELECT * COL_ALIAS FROM EMP; Action: Either specify individual columns or do not specify an alias with a "*". ORA-00966: missing TABLE keyword Cause: A LOCK statement was specified and the keyword TABLE was missing, misspelled, or misplaced. A LOCK statement must begin with LOCK TABLE tablename. Action: Correct the syntax. ORA-00967: missing WHERE keyword Cause: The keyword WHERE in a SELECT statement was missing, misspelled, or misplaced. Action: Correct the syntax. ORA-00968: missing INDEX keyword Cause: The keyword INDEX in a CREATE UNIQUE INDEX or VALIDATE INDEX statement was missing, misspelled, or misplaced. Action: Correct the syntax. ORA-00969: missing ON keyword Cause: The keyword ON in a GRANT, REVOKE, or CREATE INDEX statement was missing, misspelled, or misplaced. Action: Check syntax and spelling, and use the keyword ON where required. ORA-00970: missing WITH keyword Cause: The keyword START was specified without the keyword WITH. Both keywords are necessary if a START WITH clause is desired in a tree-structured query. Action: Change the keyword START to the keywords START WITH. Then retry the statement. ORA-00971: missing SET keyword Cause: The keyword SET in an UPDATE statement is missing, misspelled, or misplaced. Action: Check syntax and spelling, and use the keyword SET after the name of the table to be updated. ORA-00972: identifier is too long Cause: The name of a schema object exceeds 30 characters. Schema objects are tables, clusters, views, indexes, synonyms, tablespaces, and usernames. Action: Shorten the name to 30 characters or less. ORA-00974: invalid PCTFREE value percentage Cause: The percentage of free space specified in a CREATE INDEX statement is not between 0 and 100. A PCTFREE value of 0 means the entire block is available. The value 100 is not useful because it means that no data may be inserted. The default is 10. Action: Specify a PCTFREE value between 0 and 100. Then retry the statement. ORA-00975: date + date not allowed Cause: An attempt was made to add two date fields together. Dates may be added only to numeric fields, not to other dates. Action: Use the Oracle function TO_NUMBER to convert one of the date fields to a numeric field before adding it to the other date field. ORA-00976: LEVEL, PRIOR, or ROWNUM not allowed here Cause: The use of the PRIOR clause, the pseudo-column LEVEL, or ROWNUM is incorrect in this context. Action: Check the syntax for the SQL statement. Then remove or relocate the keyword PRIOR, LEVEL, or ROWNUM. ORA-00977: duplicate auditing option Cause: An AUDIT or NOAUDIT statement specified the same option more than once. Action: Either specify ALL without other auditing options or remove the duplicate auditing specifications. ORA-00978: nested group function without GROUP BY Cause: A group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, was used within another group function, as in MAX(COUNT(*)), without a corresponding GROUP BY clause. Action: Either add a GROUP BY clause or remove the extra level of nesting. ORA-00979: not a GROUP BY expression Cause: The GROUP BY clause does not contain all the expressions in the SELECT clause. SELECT expressions that are not included in a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, must be listed in the GROUP BY clause. Action: Include in the GROUP BY clause all SELECT expressions that are not group function arguments. ORA-00980: synonym translation is no longer valid Cause: The synonym used is based on a table, view, or synonym that no longer exists. Action: Replace the synonym with the name of the object it references or re-create the synonym so that it refers to a valid table, view, or synonym. ORA-00981: cannot mix table and system auditing options Cause: Both table-wide and system-wide options were specified within a single AUDIT statement. Action: Check the AUDIT command syntax. Then retry one or more AUDIT statements. ORA-00982: missing plus sign Cause: A left parenthesis appeared in a join condition, but a plus sign (+) did not follow. A left parenthesis in a join condition usually signals an outer-join specification and so a plus sign is expected to follow. To specify an outer join on a column in a join operation, follow the column reference in the join condition with a plus sign (+) enclosed in parentheses. Action: Correct the SQL syntax and retry the statement. ORA-00984: column not allowed here Cause: A column name was used in an expression where it is not permitted, such as in the VALUES clause of an INSERT statement. Action: Check the syntax of the statement and use column names only where appropriate. ORA-00985: invalid program name Cause: Probably a syntax error. Action: Correct syntax. ORA-00986: missing or invalid group names(s) Cause: Probably a syntax error. Action: Correct syntax. ORA-00987: missing or invalid username(s) Cause: No username was specified in a GRANT statement or one of the specified usernames is invalid. Valid usernames must be specified following the keyword TO in a GRANT statement to define a user. A username must begin with a letter, consist only of alphanumeric characters and the special characters $, _, and #, and be less than or equal to 30 characters. If it contains other characters, it must be enclosed in double quotation marks. It may not be a reserved word. Action: Specify a valid username, or list of usernames, following the keyword TO in the GRANT statement. ORA-00988: missing or invalid password(s) Cause: More usernames than passwords were specified in a GRANT statement. A valid password must be specified for each username listed in the GRANT statement. Action: Enter a valid password for each username. ORA-00989: too many passwords for usernames given Cause: More passwords than usernames were specified in a GRANT statement. Only one password may be entered for each username listed in the GRANT statement. Action: Enter an equal number of usernames and passwords. ORA-00990: missing or invalid privilege Cause: No privileges were specified in a GRANT privilege statement, or one of the specified privileges is invalid. Action: Enter one or more valid privileges such as SELECT, INSERT, DELETE, UPDATE, ALTER, INDEX, REFERENCES, or ALL. More than one privilege may be granted by entering the privileges in a list separated by commas (,) or by specifying the keyword ALL to grant all privileges. ORA-00992: invalid format for REVOKE command Cause: An invalid form of the REVOKE command was entered. There are two forms of the REVOKE command. The first form is used to revoke a user's database access privileges. The second form is used to revoke a user's object privileges. Action: Check the command syntax and retry the statement. ORA-00993: missing GRANT keyword Cause: The keyword WITH was specified at the end of a GRANT statement without the keyword GRANT. To grant privileges to a user and the permission to grant those privileges to another user, you must specify the keywords WITH GRANT OPTION at the end of the GRANT statement. Action: Change the keyword WITH to the keywords WITH GRANT OPTION, then retry the statement. ORA-00994: missing OPTION keyword Cause: The keywords WITH GRANT were specified at the end of a GRANT statement without the keyword OPTION. Action: Change the keywords WITH GRANT to the keywords WITH GRANT OPTION. Then retry the statement. ORA-00995: missing or invalid synonym identifier Cause: In a CREATE or DROP SYNONYM statement, the synonym name was either missing or invalid. Action: Check syntax and spelling. A valid synonym name must be specified immediately following the keyword SYNONYM in both statements. Valid synonym names must begin with a letter, consist of alphanumeric characters and the special characters $, _, and #, and be less than or equal to 30 characters. They may not be reserved words. ORA-00996: the concatenate operator is ||, not | Cause: A single bar (|) was interpreted as an attempt to specify concatenation, but the concatenation operator is a double bar (||). Action: Enter a double bar (||) for concatenation or remove the single bar (|) if concatenation was not intended. ORA-00997: illegal use of LONG datatype Cause: A value of datatype LONG was used in a function or in a DISTINCT, WHERE, CONNECT BY, GROUP BY, or ORDER BY clause. A LONG value can only be used in a SELECT clause. Action: Remove the LONG value from the function or clause. ORA-00998: must name this expression with a column alias Cause: An expression or function was used in a CREATE VIEW statement, but no corresponding column name was specified. When expressions or functions are used in a view, all column names for the view must be explicitly specified in the CREATE VIEW statement. Action: Enter a column name for each column in the view in parentheses after the view name. ORA-00999: invalid view name Cause: In a CREATE VIEW statement, the view name was missing or invalid. Action: Enter a valid view name following CREATE VIEW. Valid view names must begin with a letter, consist of only alphanumeric characters and the special characters $, _, and #, be less than or equal to 30 characters, and may not be reserved words. If the view name contains other characters, it must be enclosed in double quotation marks.
Group function is not allowed here
You cannot use an aggregate function in a WHERE clause.
Given your use case, you probably want a subquery:
select c.Numcom,c.Nompr,c.salaire_fix from commercialv c where c.salaire_fix=(select max(salaire_fix) from comercialv);
ORA-00934: group function is not allowed here
ORA-00934: group function is not allowed here
I got ” ORA-00934: group function is not allowed here ” error in Oracle database.
ORA-00934: group function is not allowed here
Details of error are as follows.
SELECT name, surname count(*) FROM employee WHERE Count(*) > 1 GROUP BY surname; ORA – 00934: GROUP FUNCTION IS NOT ALLOWED HERE 00964, 00000 – “group function is not allowed here”
GROUP FUNCTION IS NOT ALLOWED HERE
This ORA-00934 error is related to the group function which is not allowed.
You need to use HAVING CLAUSE For filtering if you use Aggregate function ( AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, ) in where clause.
To solve this error, use HAVING clause instead of where like following.
SELECT name, surname count(*) FROM employee GROUP BY surname HAVING Count(*) > 1;
1,195 views last month, 2 views today
ORA-00937: not a single-group function
ORA-00937
Error ORA-00937 occurs when the GROUP BY command is improperly used. The GROUP BY command allows the user to view rows having a common field value in a single row. For example, a small business CEO wants to view employees who worked the highest number of hours by department last month. Another instance could be when a school superintendent wants to view students on the honor roll by homeroom. In such cases, the user may run a GROUP BY statement to view the results.
GROUP BY is used in conjunction with aggregate functions to filter the results by a value. The GROUP BY command can be very useful for viewing a select set of results. It is commonly used, which deems it essential to utilize the proper syntax when running the statement.
The Problem
When faced with ORA-00923, the error message you will see is
ORA-00923: not a single-group group function
Oracle documentation states the cause as the following:
A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.
In other words, you tried to execute a SELECT statement that requires a GROUP BY clause without including the GROUP BY clause. If you are using an aggregate function in your select query (e.g. AVG, COUNT, MAX, MIN…), you must have a GROUP BY clause.
The Solution
To resolve the error, you can either remove the group function or column expression from the SELECT clause or you can add a GROUP BY clause that includes the column expressions.
If you choose to add the GROUP BY clause, make sure to include the column expressions and follow the correct order. Take the example of the small business CEO who wants to view a list of employees who worked the most number of hours, organized by department. The correct syntax that includes the GROUP BY clause would be
SELECT department, MAX(hours) AS “most hours”
FROM employees
GROUP BY department;
Looking Forward
Remember, if you are using an aggregate function in your select query then you must also have a GROUP BY clause. You cannot refer to a nonaggregated column in SELECT that is not also named in the GROUP BY clause. For the query to run successfully you must either remove the group function or column expression from SELECT or you must add a GROUP BY clause that includes the column expression.
Following this rule and ensuring proper query syntax should prevent error ORA-00937 from occurring in the future. Even though the process of correcting this error is not too difficult, contact your database administrator or licensed Oracle consultant if you continue to face problems with ORA-00937.
Using the Group Functions Questions
Using the Group Functions Questions
Advertisements
1. Which of the following is NOT a GROUP BY function?
MAX MIN NVL AVG
Answer: C. NVL is a general function used to provide alternate value to the NULL values. The functions MAX, MIN and AVG can be used as GROUP BY functions.
2. Which of the following functions can be used without GROUP BY clause in SELECT query?
COUNT MAX MIN AVG
Answer: A, B, C, D. All the listed group functions can be used in a query provided no other columns are selected in the SELECT query.
3. Which of the following SELECT query returns the department number with maximum salary compensated to an employee? (Consider the table structure as given)
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
SELECT department_id , max(salary ) FROM employees ; SELECT department_id , max(salary ) FROM employees GROUP BY department_id ; SELECT max(salary ) FROM employees GROUP BY department_id ; SELECT max(salary ) FROM employees ;
Answer: B. The MAX function can be used to return the maximum salary in a department where each group is formed by a department.
4. Which of the following statements are true about the COUNT function?
The COUNT function counts the number of rows The COUNT(*) function counts the number of rows with duplicates and NULL values The COUNT(DISTINCT) function counts the number of distinct rows COUNT(*) is equivalent to COUNT(ALL)
Answer: B. The COUNT(*) counts the number of rows including duplicates and NULLs. Use DISTINCT and ALL keyword to restrict duplicate and NULL values.
5. What are the appropriate data types accepted by GROUP BY functions?
Nested Tables NUMBER CLOB DATE
Answer: B. The data types for the functions with an argument may be CHAR, VARCHAR2, NUMBER or DATE.
6. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.
SELECT COUNT (*) FROM t_count;
12 6 9 Throws exception because COUNT function doesn’t works with NULL values
Answer: A. The COUNT(*) counts the number of rows including duplicates and NULLs. Use DISTINCT and ALL keyword to restrict duplicate and NULL values.
7. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.
SELECT COUNT (num) FROM t_count;
12 6 9 Throws exception because COUNT function doesn’t works with NULL values
Answer: C. COUNT (column) ignores the NULL values but counts the duplicates.
8. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.
SELECT COUNT (ALL num) FROM t_count;
12 6 9 Throws exception because COUNT function doesn’t works with NULL values
Answer: C. COUNT(ALL column) ignores the NULL values but counts the duplicates.
9. A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.
SELECT COUNT (DISTINCT num) FROM t_count;
12 6 9 Throws exception because COUNT function doesn’t works with NULL values
Answer: B. COUNT (DISTINCT column) counts the distinct not null values.
10. What happens when the below query is executed in SQL* Plus?
SELECT COUNT() FROM dual;
Executes successfully and returns no output Executes successfully and returns output as ‘1’ Throws exception “ORA-00909: invalid number of arguments” Throws exception “ORA-00904: “COUNT”: invalid identifier” because COUNT function doesn’t works with DUAL table
Answer: C. COUNT function requires minimum one argument which can be either the column with [ALL | DISTINCT] modifier or ‘*’.
11. Here are few statements about VARIANCE function in SQL.
i. The function accepts multiple numeric inputs and returns variance of all the values
ii. The function accepts a number column and returns variance of all column values including NULLs
iii. The function accepts a number column and returns variance of all column values excluding NULLs
i and iii i and ii ii iii
Chose the correct combination from the below options.
Answer: C. The VARIANCE function accepts single numeric argument as the column name and returns variance of all the column values considering NULLs.
12. Which of the following is NOT a GROUP BY extensions in SQL?
GROUP BY GROUPING SETS CUBE ROLLUP
Answer: A. GROUPING SETS operations can be used to perform multiple GROUP BY aggregations with a single query.
13. Select the correct statements about the below query. Consider the table structure as given.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
SELECT department_id , SUM(salary ) FROM employees GROUP BY department_id ;
SUM is a group by function because it processes group of employees working in a department SUM is an aggregate function because it produces one result per group of data SUM is a single row function because it returns single value for a group i.e. department SUM is a group by extension function because it uses GROUP BY clause to logically group the departments
Answer: A. SUM is a group function which calculates the sum of salaries of a group of employees working in a department.
14. Which clause is used to filter the query output based on aggregated results using a group by function?
WHERE LIMIT GROUP WHERE HAVING
Answer: D. HAVING Clause is used for restricting group results. You use the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the basis of aggregate information. The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical. Groups are formed and group functions are calculated before the HAVING clause is applied to the groups in the SELECT list.
15. Examine the given table structure and predict the outcome of the following query.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
SELECT count(*) FROM employees WHERE comm = NULL;
The query returns the number of employees who have no commission The query throws error because equal sign cannot be used when searching for NULL value The query returns the number of employees in a department whose commission is NULL value The query throws error because GROUP BY clause is missing in the query
Answer: B. Excluding out NULLs using WHERE condition is a way to direct the query to ignore NULLs. But here the usage of IS NULL operator is wrong. The condition should be ‘WHERE comm IS NULL’.
16. Which of the following statements is true about the group functions?
The MIN function can be used only with numeric data. The MAX function can be used only with date values. The AVG function can be used only with numeric data. The SUM function canít be part of a nested function.
Answer: C. The AVG function can be only used with numeric values. Other functions which have such restriction are SUM, STDDEV and VARIANCE.
17. Which of the following is a valid SELECT statement?
SELECT AVG(retail-cost) FROM books GROUP BY category; SELECT category, AVG(retail-cost) FROM books; SELECT category, AVG(retail-cost) FROM books WHERE AVG(retail-cost) > 8.56 GROUP BY category; SELECT category, AVG(retail-cost) Profit FROM books GROUP BY category HAVING profit > 8.56;
Answer: A. Column aliases cannot be used in GROUP BY or HAVING clause.
18. Which of the following statements is correct?
The WHERE clause can contain a group function only if the function isnít also listed in the SELECT clause. Group functions canít be used in the SELECT, FROM, or WHERE clauses. The HAVING clause is always processed before the WHERE clause. The GROUP BY clause is always processed before the HAVING clause.
Answer: D. Though Oracle doesn’t raise error if HAVING clause precedes the GROUP BY clause but it is processed only after the GROUP BY clause is processed and group are ready to be filtered.
19. Which of the following is not a valid SQL statement?
SELECT MIN(pubdate) FROM books GROUP BY category HAVING pubid = 4; SELECT MIN(pubdate) FROM books WHERE category = ‘COOKING’; SELECT COUNT(*) FROM orders WHERE customer# = 1005; SELECT MAX(COUNT(customer#)) FROM orders GROUP BY customer#;
Answer: A.
20. Which of the following statements is correct?
The COUNT function can be used to determine how many rows contain a NULL value. Only distinct values are included in group functions, unless the ALL keyword is included in the SELECT clause. The WHERE clause restricts which rows are processed. The HAVING clause determines which groups are displayed in the query results.
Answer: C, D. The WHERE clause restricts the rows before they are grouped and processed while HAVING clause restricts the groups.
21. Which of the following is a valid SQL statement?
SELECT customer#, order#, MAX(shipdate-orderdate) FROM orders GROUP BY customer# WHERE customer# = 1001; SELECT customer#, COUNT(order#) FROM orders GROUP BY customer#; SELECT customer#, COUNT(order#) FROM orders GROUP BY COUNT(order#); SELECT customer#, COUNT(order#) FROM orders GROUP BY order#;
Answer: B. The GROUP BY clause must contain all the columns except the one which is used inside the group function.
22. Which of the following SELECT statements lists only the book with the largest profit?
SELECT title, MAX(retail-cost) FROM books GROUP BY title; SELECT title, MAX(retail-cost) FROM books GROUP BY title HAVING MAX(retail-cost); SELECT title, MAX(retail-cost) FROM books; None of the above
Answer: A.
23. Which of the following statement(s) is/are correct?
1. A group function can be nested inside a group function.
2. A group function can be nested inside a single-row function.
3. A single-row function can be nested inside a group function.
1 2 3 1 and 3
Answer: A, B, C. Group functions can be nested only to a depth of two. Group functions can be nested inside single-row functions (AVG embedded in a TO_CHAR function). In addition, single-row functions can be nested inside group functions.
24. Which of the following functions is used to calculate the total value stored in a specified column?
COUNT ADD TOTAL SUM
Answer: D. SUM function is used to get the addition of numeric values.
25. Which of the following SELECT statements lists the highest retail price of all books in the Family category?
SELECT MAX(retail) FROM books WHERE category = ‘FAMILY’; SELECT MAX(retail) FROM books HAVING category = ‘FAMILY’; SELECT retail FROM books WHERE category = ‘FAMILY’ HAVING MAX(retail); None of the above
Answer: A. Since the category FAMILY has to be restricted before grouping, table rows must be filtered using WHERE clause and not HAVING clause.
26. Which of the following functions can be used to include NULL values in calculations?
SUM NVL MAX MIN
Answer: B.NVL is a general function to provide alternate values to the NULL values. It can really make a difference in arithmetic calculations using AVG, STDDEV and VARIANCE group functions.
27. Which of the following is not a valid statement?
You must enter the ALL keyword in a group function to include all duplicate values. The AVG function can be used to find the average calculated difference between two dates. The MIN and MAX functions can be used on VARCHAR2 columns. All of the above
Answer: A. The ALL keyword counts duplicates but ignores NULLs. Duplicates are also included with ‘*’ and column name specification.
28. Which of the following SQL statements determines how many total customers were referred by other customers?
SELECT customer#, SUM(referred) FROM customers GROUP BY customer#; SELECT COUNT(referred) FROM customers; SELECT COUNT(*) FROM customers; SELECT COUNT(*) FROM customers WHERE referred IS NULL;
Answer: B. Considering all customers as one group, COUNT(referred) will count only those who are referred by someone. COUNT(referred) will ignore NULL values of the column.
29. Determine the correct order of execution of following clauses in a SELECT statement.
1.SELECT
2.FROM
3.WHERE
4.GROUP BY
5.HAVING
6.ORDER BY
2-3-4-5-1-6 1-2-3-4-5-6 6-5-4-3-2-1 5-4-2-3-1-6
Answer: A. Processing order starts from FROM clause to get the table names, then restricting rows using WHERE clause, grouping them using GROUP BY clause, restricting groups using HAVING clause. ORDER BY clause is the last one to be processed to sort the final data set.
30. Which of the below clauses is used to group a set of rows based on a column or set of columns?
HAVING WHERE GROUP BY GROUPING
Answer: C. GROUP BY clause forms the groups of the data based on the column list specified.
31. Which of the following group functions can be used for population variance and population standard deviation problems?
VAR_POP STDDEV_POP VARIANCE STDDEV_SASMP
Answer: A, B.
32. Select the positions in a SELECT query where a group function can appear.
SELECT statement WHERE clause ORDER BY clause GROUP BY clause
Answer: A, C, D. Group functions can appear in SELECT, ORDER BY and HAVING clause. Oracle raises exception if group functions are used in WHERE or GROUP BY clauses.
33. Examine the structure of the EMPLOYEES table as given. Which query will return the minimum salary in each department?
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
SELECT department_id , MIN (salary ) from EMPLOYEES ; SELECT department_id , MIN (salary ) from EMPLOYEES GROUP BY department_id ; SELECT department_id , MIN (salary ) from EMPLOYEES GROUP BY salary ; SELECT department_id , MIN (salary ) from EMPLOYEES GROUP BY employee_id ;
Answer: B. MIN function returns the minimum salary in a group formed by department.
34. Examine the structure for the table EMPLOYEES and Interpret the output of the below query
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
SELECT COUNT(*), COUNT(all comm) FROM employees ;
It throws error because only one aggregate function can be used in a query. It throws error because GROUP BY clause is missing. It executes successfully and returns same values for both. It executes successfully where COUNT(*) including NULLs and COUNT(all comm) excluding NULLs.
Answer: D.
35. Which of the following are true about group functions?
You can use group functions in any clause of a SELECT statement. You can use group functions only in the column list of the select clause and in the WHERE clause of a SELECT statement. You can mix single row columns with group functions in the column list of a SELECT statement by grouping on the single row columns. You can pass column names, expressions, constants, or functions as parameter to an group function.
Answer: C. Group functions can be nested only to a depth of two. Group functions can be nested inside single-row functions (AVG embedded in a TO_CHAR function). In addition, single-row functions can be nested inside group functions.
36. Examine the structure of the table EMPLOYEES as given. You want to create a “emp_dept_sales” view by executing the following SQL statements.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
CREATE VIEW emp_dept_sales AS SELECT d.department_name , sum(e.salary ) FROM employees e, departments d where e.department_id =d.department_id GROUP by d.department_name ;
Which statement is true regarding the execution of the above statement?
The view will be created and you can perform DLM operations on the view The view will not be created because the join statements are not allowed for creating a view The view will not be created because the GROUP BY clause is not allowed for creating a view The view will be created but no DML operations will be allowed on the view
Answer: D. Rules for Performing DML Operations on a View. You cannot add data through a view if the view includes group functions or a GROUP BY clause or DISTINCT keyword. The pseudo column ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view.
37. Which of the following statements are true regarding views?
A sub query that defines a view cannot include the GROUP BY clause A view is created with the sub query having the DISTINCT keyword can be updated A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated
Answer: C, D. Rules for Performing DML Operations on a View. You cannot add data through a view if the view includes group functions or a GROUP BY clause or DISTINCT keyword. The pseudo column ROWNUM keyword Columns defined by expressions NOT NULL columns in the base tables that are not selected by the view.
38. Examine the table structure as given.
SQL> DESC departments Name Null? Type ———————– ——– —————- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4)
Which clause in the below SQL query generates error?
SELECT department_id , avg(salary ) FROM departments WHERE upper(job) in (‘SALES’,’CLERK’) GROUP BY job ORDER BY department_id ;
WHERE SELECT ORDER BY GROUP BY
Answer: D. GROUP BY clause must contain all the columns appearing in the SELECT statement. It raises error because JOB is not a selected column. It should have used DEPARTMENT_ID in placed of JOB.
39. Examine the table structure as given.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
Which of the below SELECT query will display the maximum and minimum salary earned by each job category?
SELECT job, MAX(salary ), MIN (salary ) FROM employees GROUP BY department_id ; SELECT job, MAX(salary ), MIN (salary ) FROM employees GROUP BY job; SELECT job, MAX(salary ), MIN (salary ) FROM employees ; Two aggregate functions cannot be used together in SELECT statement.
Answer: B. More than one group function can appear in the SELECT statement.
40. Consider the table structure as given.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
Examine the error in the below query.
SELECT department_id FROM employees WHERE hiredate > ’01-JAN-1985′ AND COUNT(*) > 2 GROUP by department_id HAVING SUM (salary ) > 1000;
It executes successfully and generates the required result. It produces an error because COUNT(*) should be specified in the SELECT clause also. It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*). It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause.
Answer: D. Group functions cannot be used in WHERE clause. The can appear in SELECT, HAVING and ORDER BY clause.
41. Examine the table structure as given.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
Predict the outcome of the below query
SELECT job, COUNT(employee_id ),sum(salary ) FROM employees GROUP BY job HAVING SUM (salary ) > 5000;
It executes successfully and lists the count of employees under each job category but ignores the HAVING clause since “salary ” is not in GROUP BY clause. It throws error because HAVING clause is invalid. It throws error because “salary ” is not included in the GROUP BY clause. It executes successfully and lists the count of employees under each category having sum of salary greater than 5000.
Answer: D. The HAVING clause restricts the group results. COUNT function is used for counting while SUM is used for adding the numeric values.
42. What is true of using group functions on columns that contain NULL values?
Group functions on columns ignore NULL values. Group functions on columns returning dates include NULL values. Group functions on columns returning numbers include NULL values. Group functions on columns cannot be accurately used on columns that contain NULL values.
Answer: A. Except COUNT function, all the group functions ignore NULL values.
43. Which of the following statetments are true about the usage of GROUP BY columns in a subquery?
Subqueries can contain GROUP BY and ORDER BY clauses. Subqueries cannot contain GROUP BY and ORDER BY clauses. Subqueries can contain ORDER BY but not the GROUP BY clause. Subqueries cannot contain ORDER BY but can have GROUP BY clause.
Answer: A. Like the primary query, a subquery can contain a GROUP BY as well as ORDER BY clause.
Examine the table structure as given and answer the questions 44 to 49 that follow.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
44. Predict the outcome of the below query
SELECT avg(max(salary )) FROM employees GROUP BY department_id HAVING avg(max(salary ))>100;
It executes successfully. It gives an error because the HAVING clause is not valid. It gives an error because the GROUP BY expression is not valid. It gives an error because aggregate functions cannot be nested in SELECT statement.
Answer: B. The HAVING clause doesn’t allows nesting of aggregate functions.
45. Predict the output of the below query
SELECT avg(salary ), department_id FROM employees GROUP BY department_id ;
It gives error because an aggregate function cannot appear just after SELECT clause. It gives error because GROUP BY clause is invalid. It executes without errors but produces no output. It executes successfully and gives average salary in each department.
Answer: D. Group functions can be used in any sequence (before or after the group by columns) in a SELECT query.
46. Predict the output of the below query
SELECT lower(job),avg(salary ) FROM employees GROUP BY upper(job);
It executes successfully and displays “job” in lower case. It executes successfully but display “job” in original case. It throws error because singe row and aggregate functions cannot be used together. It throws error because case conversion in the SELECT list mismatches with the case conversion GROUP BY clause.
Answer: D. The function LOWER, being a single row function must be specified in the GROUP BY clause to base the grouping of EMPLOYEES data.
47. Which of the below query executes successfully?
SELECT employee_id , COUNT(hiredate-sysdate) FROM employees ; SELECT AVG(salary ), MAX(salary ) FROM employees ; SELECT AVG(salary ), MAX(salary ) FROM employees GROUP BY department_id ; SELECT AVG(hiredate) FROM employees ;
Answer: B, C. The first query operates of the whole EMPLOYEES data while the second one processes the data in groups of department.
48. Identify the error in the below SELECT statement.
SELECT department_id , AVG (salary ) FROM employees GROUP BY department_id HAVING department_id > 10;
It executes successfully and displays average salary of departments higher than 10. It throws error because non aggregated column cannot be used in HAVING clause. It executes successfully but displays wrong result for the departments. It throws error because HAVING clause must be placed before GROUP BY clause.
Answer: A. GROUP BY expressions can be used in HAVING clause to filter out the groups from the final data set.
49. Predict the output of the below query
SELECT department_id , AVG (salary ) FROM employees GROUP BY department_id HAVING (department_id >10 and AVG(salary )>2000);
It throws error because multiple conditions cannot be given in HAVING clause. It throws error because a non aggregate column cannot be used in HAVING clause. It executes successfully and displays average salary of department higher than 10 and greater than 2000. It executes successfully but no result is displayed.
Answer: C. The HAVING clause can impose multiple conditions joined using AND or OR operator filter the groups.
50. Which of the following group functions can be used with DATE values?
AVG MIN SUM COUNT
Answer: B, D. The group function AVG and SUM can be used with numeric data only.
51. Which of the following statements are true?
AVG and SUM can be used only with numeric data types. STDDEV and VARIANCE can be used only with numeric data types. MAX can be used with LONG data type. MAX and MIN cannot be used with LOB or LONG data types.
Answer: A, B, D. The group functions AVG,SUM, VARIANCE and STDDEV can be used with numeric data only. None of the group functions can be used with LONG data type.
52. Examine the table structure as given.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
Identify the error in the below query.
SELECT department_id , avg(salary ), count(hiredate) FROM employees GROUP BY department_id ;
Multiple aggregate functions cannot be used in a single SELECT query GROUP BY clause is invalid COUNT function cannot be used with DATE values No errors and it executes successfully
Answer: D.
53. Which of the following group function can be used with LOB data types?
MAX MIN COUNT None of these
Answer: D. No aggregate function can be used with LOB data types.
54. Examine the table structure as given.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
Predict the output of the below two queries
Query – 1
SELECT avg(comm) FROM employees ;
Query – 2
SELECT avg(nvl(comm,0)) FROM employees ;
Both the queries produce same result Query – 1 and Query – 2 produce different results because Query-1 considers NULL values of COMM and Query-2 substitutes NULL values of COMM with zero Query – 1 produces error because COMM has NULL values Query – 2 produces error because NVL cannot be nested with aggregate function.
Answer: B. The AVG function ignores NULL values while calculating the average of numeric data. AVG(column) will calculate average for only non null values. However, if NVL is used to substitute NULLs with a zero, all the values will be considered.
55. Choose the correct statements about the GROUP BY clause.
Column alias can be used in the GROUP BY clause. GROUP BY column must be in the SELECT clause. GROUP BY clause must appear together with HAVING clause a SELECT query. GROUP BY clause must appear after WHERE clause in a SELECT query.
Answer: D. As per the processing sequence, the GROUP BY clause must appear after the WHERE clause in a SELECT query.
56. Examine the table structure as given.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
Predict the outcome of the below query
SELECT department_id ,avg(salary ) FROM employees GROUP BY department_id , job ORDER BY department_id ;
It throws error because GROUP BY column list doesn’t matches with SELECT column list. It executes successfully and produces average salary of a job category in each department. It executes successfully and produces average salary for a department in each job category. It throws error because GROUP BY and ORDER BY clause have different list of columns.
Answer: B. Though GROUP BY clause implicitly sorts the groups, the GROUP BY and ORDER BY clauses can be used together in a query.
57. Which clause should you use to exclude group results in a query using group functions?
WHERE HAVING GROUP BY ORDER BY
Answer: B. HAVING clause is used to restrict the groups.
Examine the table structure as given and answer the questions 58 and 59 that follow.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
58. Predict the outcome of the below query
SELECT department_id ,avg(salary ) FROM employees HAVING avg(salary )>2000 GROUP BY department_id ORDER BY department_id
It executes successfully. It throws error because HAVING clause precedes the GROUP BY clause. It throws error because HAVING clause uses the aggregate function. It executes but no results are displayed because HAVING clause precedes the GROUP BY clause.
Answer: A. HAVING clause can precede the GROUP BY clause but it is processed only after the group results are calculated.
59. Predict the outcome of the below query
SELECT department_id , COUNT(first_name ) FROM employees WHERE job IN (‘SALESMAN’,’CLERK’,’MANAGER’,’ANALYST’) GROUP BY department_id HAVING AVG(salary ) BETWEEN 2000 AND 3000;
It returns an error because the BETWEEN operator cannot be used in the HAVING clause. It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement. It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column. It executes successfully.
Answer: D. The WHERE clause restricts the number of rows participating in group clause processing.
60. Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement?
The HAVING clause can be used with group functions in subqueries. The WHERE clause can be used to exclude rows after dividing them into groups. The WHERE clause can be used to exclude rows before dividing them into groups. The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
Answer: A, C. WHERE and HAVING clause can be used together in a query. WHERE excludes the rows before group processing while HAVING restricts the groups.
Examine the table structure as given and answer the questions 61 and 62 that follow.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
61. Predict the outcome of the below query.
SELECT department_id , avg(salary ) FROM employees HAVING avg(salary ) > min(salary ) GROUP BY department_id ;
It throws an error because the aggregate functions used in HAVING clause must be in SELECT list. It throws an error because the HAVING clause appears before GROUP BY clause. It displays the departments whose average salary is greater than the minimum salary of the department. It displays the departments whose average salary is greater than the minimum salary of the organization.
Answer: C. Group functions can be used by HAVING clause to filter the groups.
62. Interpret the output of the below query.
SELECT SUM(AVG(LENGTH(first_name ))) FROM employees GROUP BY department_id ;
It calculates the sum of averages of length of employee’s name in each department. It calculates the average length of employee’s name in each department. It throws error because single row function cannot be used with group functions. It throws error because group column DEPARTMENT_ID is not used in the SELECT list.
Answer: A. Group functions can be used with single row or general functions in the SELECT query.
63. Up to how many levels, the group functions can be nested?
1 2 3 No limits
Answer: B. Group functions can be nested maximum up to 2 levels. However, single row functions can be nested up to any number of levels.
64. What is the limit of number of groups within the groups created by GROUP BY clause?
1 2 3 No Limit
Answer: D. There is no limit to the number of groups and subgroups that can be formed.
65. Choose the correct statements about the HAVING clause.
The HAVING clause is an optional clause in SELECT statement. The HAVING clause is a mandatory clause if SELECT statement uses a GROUP BY clause. The HAVING clause can appear in a SELECT statement only if it uses a GROUP BY clause. The HAVING clause is a mandatory clause if SELECT statement uses a GROUP BY clause.
Answer: A, C. HAVING clause can only appear in a query if GROUP BY clause is present, but vice versa is not true.
66. What is the output of the below query.
SELECT count(*) FROM dual GROUP BY dummy;
1 0 NULL Throws error because group functions cannot be applied on DUAL table.
Answer: A. The DUAL table contains single column DUMMY of type CHAR(1) whose value is ‘X’.
Based on the below scenario, answer the question from 67 to 74.
An organization has 14 employees who work on fixed salary of 1000. The company recruits 5 new employees whose salary is not yet fixed by the payroll department. However, during the month end processing, the HR payroll department generates several reports to reconcile the financial data of the organization. Examine the table structure as given.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
67. What is the output of the below query?
SELECT SUM (salary ) FROM employees ;
NULL 14000 19000 0
Answer: B. The SUM function adds the salaries of the employees.
68. What is the output of the below query?
SELECT AVG (salary ) FROM employees ;
1000 736.84 NULL 0
Answer: A. The AVG (salary ) function calculates the average of salaries and ignoring the NULL values. In this case, AVG(salary)=(14*1000)/14=1000.
69. What is the output of the below query?
SELECT AVG (nvl(salary ,0)) FROM employees ;
1000 NULL 736.84 0
Answer: C. The AVG(NVL(salary ,0)) gives an alternate value to the NULLs and enables them to participate in average calculation. In this case, (14*1000)/19 = 736.84.
70. What is the output of the below query?
SELECT VARIANCE (salary ) FROM employees ;
1000 0 NULL 204678.36
Answer: B. The VARIANCE (salary ) calculates the variance of salary column values ignoring NULLs.
71. What is the output of the below query?
SELECT VARIANCE (nvl(salary ,0)) FROM employees ;
1000 0 NULL 204678.36
Answer: D. The VARIANCE (NL(salary ,0)) calculates the variance of salary column values including NULLs.
72. What is the output of the below query?
SELECT STDDEV (salary ) FROM employees ;
1 1000 0 NULL
Answer: C. The STDDEV (salary ) calculates the standard deviation of salary column values ignoring NULLs.
73. What is the output of the below query?
SELECT STDDEV (nvl(salary ,0)) FROM employees ;
0 452.41 1000 NULL
Answer: B. The STDDEV (nvl(salary ,0)) calculates the standard deviation of salary column values including NULLs.
74. What is the output of the below query?
19,19 14,19 19,14 14,14
select count(*),count(salary ) from employees ;
Answer: C. COUNT(*) includes NULLs while COUNT(salary ) ignores NULL values.
75. Examine the table structure as given.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
Which of the below query will give the department who have more than 5 employees working in it?
SELECT department_id FROM employees WHERE COUNT(*) > 5 GROUP BY department_id ; SELECT department_id FROM employees HAVING COUNT(*) > 5; SELECT department_id FROM employees GROUP BY employee_id HAVING COUNT(*) > 5; SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 5;
Answer: D.
76. Which of the following are true about the CUBE extension of GROUP BY?
Enables performing multiple GROUP BY clauses with a single query. Performs aggregations for all possible combinations of columns included. Performs increasing levels of cumulative subtotals, based on the provided column list. None of the above
Answer: B. CUBE, ROLLUP are the GROUP BY extensions used for OLAP processing. CUBE aggregates the results whenever a new permutation of column is formed.
Use the following SELECT statement to answer below questions 77 to 82:
1 SELECT customer#, COUNT(*) 2 FROM customers JOIN orders USING (customer#) 3 WHERE orderdate > ’02-APR-09′ 4 GROUP BY customer# 5 HAVING COUNT(*) > 2;
77. Which line of the SELECT statement is used to restrict the number of records the query processes?
1 3 4 5
Answer: B. WHERE clause is used to restrict the rows before the groups are formed.
78. Which line of the SELECT statement is used to restrict groups displayed in the query results?
1 3 4 5
Answer: D. HAVING is used to restrict the group results after the group processing is over.
79. Which line of the SELECT statement is used to group data stored in the database?
1 3 4 5
Answer: C. GROUP BY clause uses the group by columns to group the data in the table.
80. Which clause must be included for the query to execute successfully?
1 3 4 5
Answer: C. Because the SELECT clause contains the CUSTOMER# column, it is mandatory to have GROUP BY clause with the CUSTOMER# column.
81. What is the purpose of using COUNT(*) in the SELECT query?
The number of records in the specified tables The number of orders placed by each customer The number of NULL values in the specified tables The number of customers who have placed an order
Answer: B. It counts the number of rows processing under a group. In this case, group is formed by the customer and COUNT(*) counts the orders placed by each customer.
82. Which of the following functions can be used to determine the earliest ship date for all orders recently processed by JustLee Books?
COUNT function MAX function MIN function STDDEV function
Answer: C. MIN function is used to retrieve the least value of the column. When used with date columns, it fetches the minimum date from the column.
83. Which of the following is not a valid SELECT statement?
SELECT STDDEV(retail) FROM books; SELECT AVG(SUM(retail)) FROM orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#; SELECT order#, TO_CHAR(SUM(retail),’999.99′) FROM orderitems JOIN books USING (isbn) GROUP BY order#; SELECT title, VARIANCE(retail-cost) FROM books GROUP BY pubid;
Answer: D. The GROUP BY clause must specify a column or set of columns contained in the SELECT clause. Here PUBID is not contained in the SELECT clause, hence the query is not valid.
84. Which of the below statements are true about the nesting of group functions?
The inner most function is resolved first. Oracle allows nesting of group function up to 3 levels. Single row functions can be nested with group functions. Oracle allows nesting of group function up to 2 levels.
Answer: A, C, D. In an expression containing nested functions, the innermost function is executed first whose result is fed into the next function moving in outwards direction. Single row functions can be well used with group functions which can be maximum nested up to 2 levels.
85. What are the statistical group functions in Oracle?
AVG STDDEV VARIANCE STATS
Answer: B, C. VARIANCE and STATS are the statistical group functions available in Oracle SQL.
86. If the SELECT list contains a column and a group functions, which of the following clause must be mandatorily included?
ORDER BY HAVING GROUP BY None of these
Answer: C. GROUP BY clause should necessarily contain the column or set of columns contained in the SELECT clause.
87. Examine the table structure as given.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
What is the best explanation as to why this SQL statement will NOT execute?
SELECT department_id “Department”, AVG (salary)”Average” FROM employees GROUP BY Department;
Salaries cannot be averaged as not all the numbers will divide evenly. You cannot use a column alias in the GROUP BY clause. The GROUP BY clause must have something to GROUP. The department id is not listed in the departments table.
Answer: B. Neither GROUP BY clause nor HAVING clause works with column alias.
88. Which of the following data types are compatible with AVG, SUM, VARIANCE, and STDDEV functions?
Only numeric data types Integers only Any data type All except numeric
Answer: A. The functions AVG, SUM, VARIANCE and STDDEV mandatorily work with numeric data type only.
Examine the table structure as given below and answer the questions 89 and 90 that follow.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
89. Which of the below query will display the number of distinct job categories working in each department?
SELECT department_id , COUNT(DISTINCT job) FROM employees GROUP BY job; SELECT department_id , COUNT(job) FROM employees GROUP BY employee_id ; SELECT department_id , COUNT(job) FROM employees GROUP BY department_id ; SELECT department_id , COUNT(DISTINCT job) FROM employees GROUP BY department_id ;
Answer: D. Use DISTINCT modifier to filter out the duplicates.
90. Evaluate this SQL statement:
SELECT employee_id , first_name , department_id , SUM(salary ) FROM employees WHERE salary > 1000 GROUP BY department_id , employee_id , first_name ORDER BY hiredate;
Why will this statement cause an error?
The HAVING clause is missing. The WHERE clause contains a syntax error. The SALARY column is NOT included in the GROUP BY clause. The HIRE_DATE column is NOT included in the GROUP BY clause.
Answer: D. All the columns appearing in SELECT and ORDER BY clause must be included in the GROUP BY clause.
91. Which of the following statements is true about the GROUP BY clause?
To exclude rows before dividing them into groups using the GROUP BY clause, you use should a WHERE clause. You must use the HAVING clause with the GROUP BY clause. Column alias can be used in a GROUP BY clause. By default, rows are not sorted when a GROUP BY clause is used.
Answer: A. Using a WHERE clause, you can exclude rows before dividing them into groups.
92. Examine the table structure as given.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
Interpret the outcome of the below query.
SELECT department_id , MIN (hiredate) FROM employees GROUP by department_id ;
The earliest hire date in the organization. The latest hire date in the organization. The earliest hire date in a department. The latest hire date in a department.
Answer: C. The query returns the earliest hired employee in each department.
93. Which statement about group functions is true?
Group functions except COUNT(*), ignore null values. A query that includes a group function in the SELECT list must include a GROUP BY clause. Group functions can be used in a WHERE clause. Group functions can only be used in a SELECT list.
Answer: A. All the group functions except COUNT(*), ignore NULL values. It is because they process the values directly contained in a specific column.
94. Which of the following clauses represent valid uses of group functions?
GROUP BY MAX(salary) ORDER BY AVG(salary) HAVING MAX(salary) > 10000 SELECT AVG(NVL(salary, 0))
Answer: B, C, D. Group functions can appear in SELECT, HAVING and ORDER BY clauses only.
95. Which of the following statements are true about the GROUP BY clause?
The last column listed in the GROUP BY clause is the most major grouping. The first column listed in the GROUP BY clause is the most major grouping. A GROUP BY clause cannot be used without an ORDER BY clause. The GROUP BY clause do not ensure the sorting of output.
Answer: B. The grouping of data is based on the sequence of columns appearing in the GROUP BY clause.
96. What is difference between WHERE clause and HAVING clause?
WHERE clause restrict rows before grouping while HAVING clause restricts groups. WHERE clause cannot contain a group function but HAVING clause can have. WHERE clause can join multiple conditions using AND or OR operators but HAVING clause cannot. WHERE clause can appear in SELECT query without GROUP BY clause but HAVING clause cannot.
Answer: A, B, D. WHERE clause restricts the rows before grouping but HAVING restricts the groups.
97. Examine the table structure as given.
SQL> DESC employees Name Null? Type ———————– ——– —————- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)
Predict the outcome of the below query.
SELECT department_id ,job,count(*) FROM employees GROUP BY department_id ,job ORDER BY department_id ,count(*);
It executes successfully. It throws error because ORDER BY clause is invalid. It throws error because GROUP BY clause is invalid. It throws error because GROUP BY and ORDER BY clause cannot be used together.
Answer: A. ORDER BY clause can use the group functions for sorting.
Oracle Native wrapper(Net8 wrapper) error: SQL1822N: “ORA-00937: not a single-group group function”
EMPNAME SALARY ——————– ———- SQL1822N Unexpected error code “937” received from data source “DATASTORE2”. Associated text and tokens are “ORA-00937: not a single-group group function “. SQLSTATE=560BD
SELECT empname,salary FROM employee x WHERE exists (SELECT salary + 10000 FROM depta08 WHERE salary = x.salary UNION SELECT avg(salary) FROM depta08 WHERE salary = x.salary)
Cause
The original statement is:
SELECT empname,salary FROM employee x WHERE exists (SELECT salary + 10000 FROM depta08 WHERE salary = x.salary UNION SELECT avg(salary) FROM depta08 WHERE salary = x.salary)
The remote query generated by federation that send to remote oracle is:
SELECT A0.”SALARY” C0, A0.”EMPNAME” C1 FROM “J15USER1″.”EMPLOYEE” A0 WHERE (EXISTS ((SELECT (A1.”SALARY” + 10000) FROM “J15USER1″.”DEPTA08″ A1 WHERE (A1.”SALARY” = A0.”SALARY”)) UNION ALL (SELECT (SUM( A2.”SALARY”) / COUNT( A2.”SALARY”)) FROM “J15USER1″.”DEPTA08″ A2 WHERE (A2.”SALARY” = A0.”SALARY”))))
SQL1822N means that remote oracle does not support the remote query and reason is: ORA-00937.
Oracle’s error message is “ORA-00937: not a single-group group function”. Its meaning is “A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.”
How to Fix a ‘Not a GROUP BY Expression’ Error
Whether you are an experienced SQL user or only a beginner, your SQL queries may return errors. The accompanying messages are supposed to help you fix them; however, sometimes the messages are not very helpful. The Oracle message “Not a GROUP BY expression” is one such example. Let’s review what this is about and how to fix it.
We are going to assume some basic understanding of how GROUP BY works. If you are not familiar with grouping data in SQL, check out this introductory article on using GROUP BY in SQL or go to our interactive SQL Basics course where you’ll learn the foundations of SQL, including GROUP BY .
If you are familiar with GROUP BY , you should recall the following general rule: each column in the SELECT statement should either be called in an aggregate function or be in the GROUP BY clause.
ORA-00979 “ Not a GROUP BY expression ” is an error issued by the Oracle database when the SELECT statement contains a column that is neither listed in GROUP BY nor aggregated. This error message can be confusing to beginners.
Let’s review an example to understand why this error occurs. Consider the following table with basic information about each of our customers: ID, name, city, state, date of the last purchase, and the total amount of purchases.
id name city state last_purchase_date purchases 1 Peter White San Francisco CA 2020-09-09 120.85 2 Helen Brown San Francisco CA 2019-04-11 24.85 3 Jeff Grey Los Angeles CA 2020-03-23 1085.00 4 Kristine Black New York NY 2020-10-02 230.50 5 Donald Page New York NY 2020-06-30 2345.00 6 Robert Lee Los Angeles CA 2019-12-06 11.00 7 Patrick Collins San Francisco CA 2020-02-12 200.30 8 Kate Nord Buffalo NY 2020-05-25 435.00
Suppose we want to know the date of the last purchase and the average purchase amount per customer by state. We use the following query:
SELECT state, city, MAX(last_purchase_date) AS last_purchase, AVG(purchases) AS avg_purchases FROM customers GROUP BY state;
If you try to run this query, the Oracle database will output the following error message:
ORA-00979: not a GROUP BY expression
From this, it is not obvious what you need to fix. Other databases provide more understandable messages:
PostgreSQL. ERROR: column “customers.city” must appear in the GROUP BY clause or be used in an aggregate function
SQL Server. Column ‘customers.city’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
So, what’s wrong with our query?
Why Does the Oracle Database Report This Error?
In the query above, we ask the database to show the following information about the customers:
state,
city,
the last purchase date, and
the total purchase amount.
Of these four columns, the state column appears in the GROUP BY clause, and the last_purchase_date and purchases columns are aggregated using MAX() and AVG() functions, respectively. The city column is neither called by an aggregate function nor is listed in GROUP BY . However, we have two cities in the state of California (“CA”) and two cities in the state of New York (“NY”). The database simply doesn’t know which value to display.
state city last_purchase avg_purchases CA San Francisco
OR
Los Angeles? MAX(last_purchase_date) AVG(purchases) NY New York
OR
Buffalo? MAX(last_purchase_date) AVG(purchases)
If you want to learn more about how GROUP BY works, check out my previous article, where I show how rows are grouped in SQL.
How to Fix the “Not a GROUP BY Expression” Error
Your approach to fixing this error will depend on what you want to achieve. Here are the options to consider.
Option 1. Add the city column to GROUP BY . If you want the city to be displayed in the output, you’ll need to group the data by both state and city .
SELECT state, city, MAX(last_purchase_date) AS last_purchase, AVG(purchases) AS avg_purchases FROM customers GROUP BY state, city;
Here is the result you’ll get when grouping by state and city :
state city last_purchase avg_purchases NY Buffalo 2020-05-25 435.00 CA San Francisco 2020-09-09 115.33 CA Los Angeles 2020-03-23 548.00 NY New York 2020-10-02 1287.75
Option 2. Remove the city column from SELECT . If you want your output to be grouped by state only, you’ll need to remove city from the SELECT statement. As I have demonstrated above, it is simply not possible to display the city when the rows are grouped only by state .
SELECT state, MAX(last_purchase_date) AS last_purchase, AVG(purchases) AS avg_purchases FROM customers GROUP BY state;
The result will be as follows:
state last_purchase avg_purchases CA 2020-09-09 288.40 NY 2020-10-02 1003.50
Option 3. Call the city column in an aggregate function. In some cases, you may want to aggregate data in the column using a function such as COUNT() , SUM() , AVG() , MAX() , or MIN() .
In our example, we may choose to display how many unique cities with customers there are in each state:
SELECT state, count(DISTINCT city) AS cities_with_customers, MAX(last_purchase_date) AS last_purchase, AVG(purchases) AS avg_purchases FROM customers GROUP BY state;
Here’s what you’ll get when you run this query:
state cities_with_customers last_purchase avg_purchases CA 2 2020-09-09 288.40 NY 2 2020-10-02 1003.50
Check out this article for more examples of GROUP BY .
Fix the “Not a GROUP BY Expression” Error and Practice More GROUP BY!
You’ve learned why the error “ Not a GROUP BY expression ” occurs and how to fix it. It’s time to put this knowledge into practice with some real-world examples. I recommend starting with these interactive courses on LearnSQL.com:
SQL Basics is an introductory SQL course that covers all basic topics, including grouping and aggregating data in SQL.
is an introductory SQL course that covers all basic topics, including grouping and aggregating data in SQL. SQL Practice Set is a set of over 80 SQL exercises to help you practice grouping and more in SQL.
is a set of over 80 SQL exercises to help you practice grouping and more in SQL. Creating Basic SQL Reports focuses on the details of GROUP BY not covered in the SQL Basics course. Here, you will learn about some common mistakes with GROUP BY and see how it is used in real-world reports.
Finally, now that you’re firmly grounded in the key rule of the GROUP BY clause – namely, all non-aggregated columns from the SELECT statement should be in GROUP BY – it turns out that this is not always the case! Intrigued? Read this article to learn more.
Happy learning!
ORA-00934: group function is not allowed here – Oracle PL/SQL Tutorial
ORA-00934: group function is not allowed here
Oracle PL/SQL error message: ORA-00934: group function is not allowed here.
Cause:
One of the group functions, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, was used in a WHERE or GROUP BY clause.
Solution:
Remove the group function from the WHERE or GROUP BY clause. The desired result may be achieved by including the function in a subquery or HAVING clause.
Example:
select s.student_id, s.first_name, s.last_name, count(o.order_id) from students s, orders o where s.student_id = o.student_id group by s.student_id, s.first_name, s.last_name, count(o.order_id) order by s.student_id;
Output:
ORA-00934: group function is not allowed here
Correct:
select s.student_id, s.first_name, s.last_name, count(o.order_id) from students s, orders o where s.student_id = o.student_id group by s.student_id, s.first_name, s.last_name order by s.student_id;
ORA-00934: group function is not allowed here
Hi all,
Good Morning. My db version is 12.2. I am getting below errors. I appreciate if someone help out this querry
SQL> ed
Wrote file afiedt.buf
1 select SEGMENT_NAME, sum(BYTES)/1024/1024/1024 SIZEGB,count(*) TOTAL_PARTIT
IONS,tablespace_name
2 FROM DBA_SEGMENTS
3 WHERE SEGMENT_TYPE=’TABLE PARTITION’
4 AND OWNER=’SYSMAN’
5 and tablespace_name in (‘MGMT_TABLESPACE’)
6 AND
7 sum(BYTES)/1024/1024/1024>2
8 GROUP BY SEGMENT_NAME,tablespace_name
9* ORDER BY SIZEGB DESC
SQL> /
sum(BYTES)/1024/1024/1024>2
*
ERROR at line 7:
ORA-00934: group function is not allowed here
Oracle SQL Throwing “ORA-00934: group function is not allowed here” error for simple Select w/AVG
For that version of AVG() , you need to GROUP BY on the columns, but that way your average value would be incorrect. Sure you can use a subquery (where you do not reference any other column, and because of that, you do not need a GROUP BY inside), for example:
SELECT COL1, COL2 FROM TABLE1 WHERE COL3 > (SELECT AVG(COL3) * 3 FROM TABLE1) ORDER BY COL1;
But this will perform 2 index/table scans, that work can be reduced by using the analytic version of AVG() :
SELECT * FROM ( SELECT COL1, COL2, COL3, AVG(COL3) OVER () * 3 AS COL3_AVG3 FROM TABLE1 ) WHERE COL3 > COL3_AVG3;
Edit: forgot about the greater than zero condition, including that:
SELECT COL1, COL2 FROM TABLE1 WHERE COL3 > (SELECT AVG(COL3) * 3 FROM TABLE1 WHERE COL3 > 0) ORDER BY COL1;
And:
ORA-00934: Group feature is not allowed here
PL/SQL: ORA-00934: Group feature is not allowed here Hello
I write a PL/SQL procedure. The structure is like:
SET SERVEROUTPUT ON;
CREATE or REPLACE procedure abc
IS
v_total_ip_rec number (14);
v_total_op_rec number (14);
v_total_rec number (14);
BEGIN
SELECT SUM (CASE
WHEN < condition 1 >
THEN 1
0 OTHERWISE
END
) in v_total_ip_rec.
SUM (CASE
WHEN < condition 2 >
THEN 1
0 OTHERWISE
END
) in v_total_op_rec.
SUM (1) in v_total_rec
OF A, B
WHERE A.Col1 = B.Col1;
EXCEPTION
WHILE OTHERS THEN
raise_application_error (-20001,’ an error has occurred – ‘ |) SQLCODE |’ – ERROR – ‘ | SQLERRM);
END;
When I run this procedure it gives me following error:
“PL/SQL: ORA-00934: Group feature is not allowed here.”
Someone has an idea?
Any help would be appreciated.
Thank you. Should I have any special role? Have you checked if synonyms exist for tables?
Please check in this order: #1-synonymes appropriate
#2-appropriate privileges
#3-appropriate roles
Group feature is not allowed here Hello
I have a problem running the following sql command, I know that the error is the count function, but I don’t know how to solve this problem. Can you please help me solve this problem.
(I like to select titles that have more than 2 authors).
Separate select titles.title_id, titles.title_name, titles.publisher_ID
authors, titles, and author_titles
where titles.title_ID = author_titles.title_id
and author_titles.au_id = authors.au_id and count (authors) > 2
ORDER BY titles.title_id DESC;
Thank Yoy Hello You group by expression is absent from the columns you have in your select statement SELECT titles.title_id, titles.title_name, titles.publisher_ID FROM authors, titles, author_titles WHERE titles.title_ID = author_titles.title_id AND author_titles.au_id = authors.au_id GROUP BY titles.title_id, titles.title_name, titles.publisher_ID HAVING COUNT ( * ) > 2 ORDER BY titles.title_id DESC; Concerning
OrionNet
Group feature is not allowed Hi all…
When I run the following text in the SQL command screen, it works fine… But when I try to use it in the APEX to fill the values on the page, it gives me error…
ERROR
1 error has occurred
ORA-06550: line 25, column 74: PL/SQL: ORA-00934: Group feature is not allowed here ORA-06550: line 24, column 1: PL/SQL: statement ignored
CODE THAT WORKS IN THE FORM OF SQL COMMAND
SELECT
SUM(CASE WHEN SLOT = ‘Q’ THEN ‘1’ ELSE ‘0’ END) AS C_SLOT,
SUM(CASE WHEN TEST = ‘P’ THEN ‘1’ ELSE ‘0’ END) ACE C_TEST,
OF TBL_REC;
CODE THAT GIVES THE ERROR ON THE APPLICATION EXPRESS PAGE
SELECT
SUM (CASE WHEN SLOT = ‘Q’, THEN ‘1’ OTHER ‘0’ END) IN: P2_C_SLOT,.
SUM (CASE WHEN TEST = ‘P’, THEN ‘1’ OTHER ‘0’ END) IN: P2_C_TEST,.
OF TBL_REC;
The GBA SLOT and TEST have the letter codes, I want just that it count only when it matches a certain letter. In the form of SQL command, it returns a single line, with the charges, all right. This is only a problem when I try to use anywere on a page in Application Express.
The full version has about 20 lines that are all rows in the sum, but I can’t even the worm 2 lines above to work.
Any thoughts? VR; SELECT
SUM (CASE WHEN SLOT = ‘Q’, THEN ‘1’ OTHER ‘0’ END)
SUM (CASE WHEN TEST = ‘P’, THEN ‘1’ OTHER ‘0’ END) IN: P2_C_SLOT,: P2_C_TEST
OF TBL_REC; Published by: seal of hash on June 3, 2009 12:56
Help with error – 934 group function is not allowed here Hey there will, I’m having problems with a request and just does not know how to do it without error. I’m trying to get all the employee emerging infectious diseases that have less than 2 number max of DID (dependants) in the table. It’s my current query SELECT Employee.LName. ‘ ‘ || Employee.Fname as Full_Name, Employee.EID The left outer JOIN employee depends on Employee.EID = Dependent.EID Having Count (DID)–2 > ((select Max (N) as From (SELECT Employee.EID, Count (DID) As N FROM Employee Inner Join Dependent On Employee.EID = Dependent.EID group by Employee.EID, Count (DID))) N) Order of Employee.Lname, Employee.Fname Which gives me an error on column 4, no matter what I do. If I remove the Count (DID) in the group by clause (which I tried it earlier), it gives me a is not an error of the function of single group… The most frustrating thing is that Select Max (N) as From (SELECT Employee.EID, Count (DID) As N FROM Employee Inner Join Dependent On Employee.EID = Dependent.EID group by Employee.EID) N Works perfectly, but because it’s a mission, I have to do in one step (no substeps/views) Any help? Thank you very much Hello ac981e5d-D10A-4520-BF42-23a894d04fb7 wrote: Ok. I’m taking your code in a view… I get this. and there is an orange underscore and a text of the error that says Select incoherent list in group by… change the group by clause of e.fname, e.lname, e.eid, count, max Which isn’t what either the Oracle database would do. Everything about orange (or any other color) sounds like it is caused by a front-end that could be interacting with Oracle. In addition, the Oracle error messages always come with a 3-letter-5 code, as ORA-00933. under the selection internal (first medium) You have deleted the WITH clause. The parser can recognize the error when it has reached the first left parenthesis. Create view AS A10T2 ( SELECT e.lname. ‘ ‘ || e.fname AS full_name e.eid (D.) AS this_group_count MAX (COUNT (d.)) ON (AS highest_group_count) E employee LEFT OUTER JOIN dependent d ON e.eid = d.eid GROUP BY e.lname, e.fname, e.eid ) SELECT full_name eid Of aggregate_results WHERE this_group_count > = highest_group_count – 2 ORDER BY full_name You need the WITH to define this clause means “AGGREGATE_RESULTS”: Create view AS A10T2 WITH aggregate_results AS ( SELECT e.lname. ‘ ‘ || e.fname AS full_name … Why do you have an ORDER BY clause in a view? (It is probably not cause of your errors, just make the inefficient view) Command line error: column 5: 23 Error report- SQL error: ORA-00933: SQL not correctly completed command 00933 00000 – “not correctly completed SQL command. * Cause: * Action: This is another indication that some front is getting involved. Looks like your front-end reports the exact Oracle error message, “0RA-00933” and then builing it’s own error code, “00933. 00000 “, on this basis. ORA-00933 is a reasonable mistake to wait if you omit the line ‘ WITH the aggregated results AS. Once again, until I can actually run your code, I can’t test it, and I can’t run your code until you post CREATE TABLE and INSERT statements for some examples of data, or change the problem to use commonly available tables, such as those in the scott schema. and when I try my code CREATE VIEW A10T2 AS SELECT Employee.LName. ‘ ‘ || Employee.Fname as Full_Name, Employee.EID The left outer JOIN employee depends on Employee.EID = Dependent.EID Seen (Count (DID)) + 2 > (select Max (N) From (SELECT Employee.EID, Count (DID) As “N” FROM Employee Inner Join Dependent On Employee.EID = Dependent.EID group by Employee.EID)) Order of Employee.Lname, Employee.Fname I get Command line error: column 2: 8 Error report- SQL error: ORA-00937: not a function of simple-group 00937 00000 – ‘not a single-group function. * Cause: * Action: Then the orange underscore even under my inner (“select employee. EID, Count (DID) as “N” “) says to change the Group of Employee.eid, Count (DID) I just don’t understand why he tells me to group them by Count (DID)? Isn’t that what you did in your original post, and I have explained in answer #2? If you continue to repeat the same mistake, you can expect continue to get the same error. Given that you have a code, you know causes an error, do you think really that what makes a vision will cause the error to disappear? The inner query works fine on its own… Right; It’s the outer query where you are missing the GROUP BY clause.
ORA-01733-virtual column not allowed here – insert using inline view Does anyone know why I get ORA-01733-virtual column not allowed here
SQL > select * from v version $;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
PL/SQL release 11.1.0.6.0 – Production
CORE 11.1.0.6.0 Production
AMT for 32-bit Windows: Version 11.1.0.6.0 – Production
NLSRTL Version 11.1.0.6.0 – Production
-no error without the WITH CHECK option
SQL > INSERT INTO
2 (SELECT
3 location_id,
4 city
5 l.country_id
6 OF country c, localities, regions r l
7 where l.country_id = c.country_id
8 and c.region_id = r.region_id
9 and r.region_name = ‘Asia’)
10 VALUES (5500, ‘Common Wansdworth’, ‘UK’);
1 line of creation.
SQL > rollback;
Complete restoration.
-error with WITH CHECK OPTION
SQL > INSERT INTO
2 (SELECT
3 location_id,
4 city
5 l.country_id
6 OF country c, localities, regions r l
7 where l.country_id = c.country_id
8 and c.region_id = r.region_id
9 and r.region_name = ‘Asia’ WITH CHECK OPTION)
10 VALUES (5500, ‘Common Wansdworth’, ‘UK’);
INSERT INTO
*
ERROR on line 1:
ORA-01733: virtual column not allowed here
I was expecting
ORA-01402: discovers the violation of where WITH CHECK OPTION clause
for the second. Am I missing here? Coskan wrote:
Randolf Thanks a lot for the update of this old question
After reading the link, I think I should ignore this error and accept him as ORA-01402 The information that you have asked me to check me do not have an understanding of the different error types. Coskan, I didn’t know this is an old thread that somehow got updated by someone else. Regarding your question: you’re right that the output of the script is not really that useful. I have just run on 10.2.0.4 and in general, it seems that the output of USER_UPDATABLE_COLUMNS is incorrect regarding the views of join using the WITH CHECK OPTION. For example although the location_id from the TEST_V_2 column appears as non-editable (probably because of the rule that “the columns used in the join expression” cannot be modified in a join view when you use the WITH CHECK OPTION) I can run successfully your insert if statement I choose a location_id less than 2000. It seems that summed up the difference if you join more than two tables, you’ll always get the error “ORA-01733” when you try to insert in the join with the enabled OPTION CHECK view. For example to add a third table TEST_V_2 which does not change the original view, but simply joined meaning for example COUNTRIES to LOCATIONS, will show the same behavior to throw an ORA-01733, however it works fine when the omission of the WITH CHECK OPTION. So overall, I tend to say it is really a limitation of the implementation and it is not actually an ORA-01402 but looks like Oracle is simply trying to tell you: Amendment No. INSERT in this possible view. Updates however seem to work, at least I can find some examples of work. There seems to be other restrictions of implementation with the WITH CHECK OPTION in place even when the only membership as two tables, for example when trying to join the COUNTRIES and PLACES but by selecting only in PLACES and by using a filter on the COUNTRY_ID predicate fails with ORA-01733 when this predicate is applied to the COUNTRY. COUNTRY_ID but it works if this predicate is applied to PLACES. COUNTRY_ID. I could imagine that Oracle has quietly added some of these restrictions with each patch set due to the angle of the case/adverse reactions encountered. I saw this for other features, too. Kind regards
Randolf Oracle related blog stuff:
http://Oracle-Randolf.blogspot.com/ SQLTools ++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676 /.
http://sourceforge.NET/projects/SQLT-pp/
ORA-00976: virtual specified or operator not allowed here Hello We had error after upgrade of GR 11, 2, in the insert statement. INSERT INTO SDE_TBL_FLEXTRIMSITROUT (BRANCHCD, SOURCECD, CURRENTNO, BATCHNO, DEPTCD, CTL INITIATIONDATE, AMOUNT, ACCOUNT, ACCOUNTBRANCH, TXNCD, DEBITCREDIT, LCYEQUIVALENT, EXCHRATE, VALUEDATE, INSTRUMENTNO, RELCUST, ADDLTEXT, TXNMIS1, TXNMIS2, TXNMIS3, TXNMIS4, TXNMIS5, TXNMIS6, TXNMIS7, TXNMIS8, TXNMIS9, TXNMIS10, COMPMIS1, COMPMIS2, COMPMIS3, COMPMIS4, COMPMIS5, COMPMIS6, COMPMIS7, COMPMIS8, COMPMIS9, COMPMIS10, COSTCODE1, COSTCODE2, COSTCODE3, COSTCODE4, COSTCODE5, RELATEDACCOUNT, RELATEDREF, USERREFERENCE, ACCTPOSTOVERWRITE, EXCHRATEOVERWRITE, VALUEDATEOVERWRITE, ACCTBALOVERWRITE, ITRREFER, RefinanceAmount, PROCESSID) VALUES (vBranchCode, cCreateNewTrimsITR_rec. APPLSYS, ROWNUM, nBatchNo, cCreateNewTrimsITR_rec. DEPT, vCcy, To_date (cCreateNewTrimsITR_rec. HOUR, ‘YYYYMMDD’), nAmount, vAccount, vAccountBranch, – added by Sibylle vTxnCd, cDebitCredit, nLcyEquivalent, nExchRate, To_date (cCreateNewTrimsITR_rec. VALUEDATE, ‘YYYYMMDD’), vInstrumentNo, –‘ ‘ || SUBSTR (cCreateNewTrimsITR_rec. ITRREFER, 2, 11), vFlxCntry | cCreateNewTrimsITR_rec. APNO, vDesc, cCreateNewTrimsITR_rec. TRANSOUC, RPAD (‘ ‘, 9); vExpenseMIS, vProductMIS, RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 9); RPAD (‘ ‘, 20); cCreateNewTrimsITR_rec. THEIRREF, RPAD (‘ ‘, 16). cActPostOverwrite, cExchRateOverWrite, cValueDateOverWrite, cAcctBalOverWrite, cCreateNewTrimsITR_rec. ITRREFER, cCreateNewTrimsITR_rec. REFIAMOUNT, nFlexOutProcessId); Error: ORA-00976: specified virtual or operator not allowed here According to the audit, which are a problem in GR 11, 2 with insert query using rownum in values. Someone knows how to fix this? I wonder why you want to use rownum as a value of insertion? It’s not really worth doing an insert without a select statement. If you want to use it, it would look like Insert into t1 (name, row_num_value) Select name rownum the t2; But then you can use an order by clause, on the other it would spoil the rownum order. So an important question is, where is the rownum used for?
ORA-00976: virtual specified or operator not allowed here – (11g) This ora error occurs on one of my test environment.
ORA-00976: virtual specified or operator not allowed here
This happens in one of the plsql pkg, which has no compilation errors. and works perfectly well in other environments.
The only difference I noticed is what error in DB, which is 11g – 11.2.0.3
and other environments that are in good shape is 10g – 10.2.0.5
Overall of the function where the error is, there is one thing which I suspect (but not sure)
It is CONNECT BY LEVEL, is used in a query. This could be a problem in 11g?
If not, any help / troubleshooting tips that will be greatly appreciated. !
-Thank you, I can reproduce your error on my 11.2.0.3 instance. Looks like the bug number 13496250 introduced in the 11.2.0.3. John
Database trigger – PL/SQL: ORA-00984: column not allowed here I am trying to create a trigger that will update a table of audit used when a row is changed. Using a sequence number to assign an identifier unique to each line as it is created. Need to capture the user ID, date modified and action (update), the image of the front line.
CREATE SEQUENCE emp_audit_seq START WITH 10; Create table emp ( empno NUMBER(4) Primary Key, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2)); CREATE TABLE emp_audit ( audit_uid NUMBER(15) Primary Key, change_date DATE, change_user VARCHAR2(30), action CHAR(1), empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2)); CREATE OR REPLACE TRIGGER trig_emp_audit BEFORE UPDATE ON emp FOR EACH ROW BEGIN INSERT INTO emp_audit VALUES(emp_audit_seq.nextval, change_date, change_user, action, :old.empno, :old.ename, :old.job, :old.mgr, :old.hiredate, :old.sal, :old.comm, deptno); END; / Warning: Trigger created with compilation errors. SQL> show errors Errors for TRIGGER TRIG_EMP_AUDIT: LINE/COL ERROR ——– ———————————————– 2/3 PL/SQL: SQL Statement ignored 3/149 PL/SQL: ORA-00984: column not allowed here Can someone help to help me find what I’m doing wrong with the trigger?
Published by: LostNoob on August 25, 2012 14:24 First of all, when you write an INSERT statement, it is always good for the columns that you insert in the list. Which makes the code easier to follow – you do not have separately pull toward the top of the table definition to know what order of columns is inserted. And it makes the code easier to manage because the declaration become invalid if you add a new column to the table in the future. Second, CHANGE_DATE, CHANGE_USER and ACTION are not (probably) functions and are not local variables so it is not supposed to use them in an INSERT statement. You need to write code or to take advantage of the existing functions to fill in these columns. I suppose, for example, that you want to use SYSDATE to fill the CHANGE_DATE and the USER to fill the column CHANGE_USER. My guess is that ACTION must always be a ‘U’ for UPDATE. Thirdly, it seems that you left the: old man on the DEPTNO column. Put them all together, you would have something like CREATE OR REPLACE TRIGGER trig_emp_audit BEFORE UPDATE ON emp FOR EACH ROW BEGIN INSERT INTO emp_audit( audit_uid, change_date, change_user, action, enpno, ename, job, mgr, hiredate, sal, comm, deptno ) VALUES( emp_audit_seq.nextval, sysdate, user, ‘U’, :old.empno, :old.ename, :old.job, :old.mgr, :old.hiredate, :old.sal, :old.comm, :old.deptno); END; / Justin
PL/SQL: ORA-00984: column not allowed here Sorry, it’s probably easy and I forgot something simple, but it’s driving me crazy 🙂
VARIABLE g_fk_deduction VARCHAR2(30) VARIABLE g_fk_empno NUMBER VARIABLE g_before_or_after_flag CHAR(1) VARIABLE g_deduction_amount NUMBER BEGIN :g_fk_deduction := ‘401K’; :g_fk_empno := 7369; :g_before_or_after_flag := ‘B’; :g_deduction_amount := 150.00; END; / BEGIN INSERT INTO emp_deductions (fk_deduction, fk_empno, before_or_after_flag, deduction_amount) VALUES (g_fk_deduction, g_fk_empno, g_before_or_after_flag, g_deduction_amount); COMMIT; END; / Error: PL/SQL: ORA-00984: column not allowed here on g_deduction_amount
in the value clause.
Any help would be appreciated.
Table is below: CREATE TABLE emp_deductions ( fk_deduction VARCHAR2(30), fk_empno NUMBER(4), before_or_after_flag CHAR(1), deduction_amount NUMBER(6,2)); Published by: LostNoob on August 23, 2012 19:06 rp0428 wrote:
>
: g_fk_deduction: = “401k”.
: g_fk_empno: = 7369;
: g_before_or_after_flag: = ‘B ‘;.
: g_deduction_amount: = 150.00;
>
Why did you put a colon here? Get rid of them. They are necessary, since they are declared SQL * more variable. The problem for the OP, is that in the clause values in the insert the colon are missing. Published by: Mark Williams on August 23, 2012 22:31 Here is your example requested: SQL> create table test (c number); Table created. SQL> variable v_c number SQL> begin 2 :v_c := 46; 3 end; 4 / PL/SQL procedure successfully completed. SQL> begin 2 insert into test values (:v_c); 3 end; 4 / PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> select * from test; C ———- 46 1 row selected. SQL>
ORA-00984: column not allowed here Hi all
Kindly someone please shed some light on this called error “ORA-00984: column not allowed here?
I created a table:
CREATE TABLE JB (ENAME VARCHAR (20));
Table created.
Now, if I INSERT:
INSERT INTO VALUES JB (sintes);
ORA-00984: column not allowed here
Why this error, if I insert a number instead of “Sinti”, implementation is also possible. No character is not allowed.
What is the column name in this area, its totally strange to me.
Please someone help me.
Thank you and best regards,
SINTES Hello
Use quotes around Zack: INSERT INTO JB VALUES (‘Sathik’);
ORA-02287: sequence number not allowed here < police = "Courier New" >
Hello
Why do I have this problem?
Insert an order tab2 select tab2_seq.nextval, eng_cat from (select distinct job_det eng_cat) by 2.
SQL > r
1 * insert into tab2_seq.nextval select eng_cat select (separate eng_cat tab2
Insert in tab2 select tab2_seq.nextval, eng_cat from (select distinct eng_cat from)
*
ERROR on line 1:
ORA-02287: sequence number not allowed here
Thank you and best regards,
Human
< / make > It’s ‘order of 2’ which makes it fail. That makes no sense at all?
Just remove the order by clause, then it works 😉
ORA-00984: column not allowed here, when you try to use a default UDF I am wanting to create a table that has a default value of a user-defined function, and because this default is intended to be used in a few different places so I would use a function rather than in the table definition.
This is my current code that works very well: CREATE TABLE DEPT_HIST ( DEPTNO NUMBER(2) not null, DNAME VARCHAR2(14), LOC VARCHAR2(13), SQL_ACTV_C CHAR(1) not null, EFFT_STRT_S TIMESTAMP(6) default SYSTIMESTAMP not null, EFFT_END_S TIMESTAMP(6) default TO_TIMESTAMP(‘9999/12/30 00:00:00.000000’, ‘YYYY/MM/DD:HH24:MI:SS.FF6’) not null, DELT_F CHAR(1) default ‘N’ not null ); but I would get something similar to this work: CREATE OR REPLACE FUNCTION EOT RETURN timestamp IS Result timestamp; BEGIN RETURN (TO_TIMESTAMP (‘9999/12/30 00:00:00.000000’, ‘YYYY/MM/DD:HH24:MI:SS.FF6’)); END EOT; / select eot from dual; EOT ————————————————————————— 30/DEC/99 12:00:00.000000000 AM CREATE TABLE DEPT_HIST ( DEPTNO NUMBER(2) not null, DNAME VARCHAR2(14), LOC VARCHAR2(13), SQL_ACTV_C CHAR(1) not null, EFFT_STRT_S TIMESTAMP(6) default SYSTIMESTAMP not null, EFFT_END_S TIMESTAMP(6) default EOT not null, DELT_F CHAR(1) default ‘N’ not null ); but I get an error of: EFFT_END_S TIMESTAMP(6) default EOT not null, * ERROR at line 8: ORA-00984: column not allowed here Any ideas? I guess I could use a trigger but not exactly what I’m after. Hello Sorry; No function defined by the user in the DEFAULT clause.
From SQL, under “CREATE TABLE Statement” language manual
http://download.Oracle.com/docs/CD/B28359_01/server.111/b28286/statements_7002.htm#sthref7119 Restriction on default column values
A DEFAULT expression cannot contain references to the functions PL/SQL or other columns, the nickname CURRVAL, NEXTVAL, LEVEL, PRIOR and ROWNUM, or date constants that are not completely specified. I wouldn’t use a trip just for that. If you need a trigger for other reasons, then maybe you can include the definition of this column in the trigger, too, but, in general, avoid triggers when there is an alternative.
ORA-00976 LEVEL, PRIOR or ROWNUM not allowed here in the PO_DOCUMENT_CH package Hello
EBS R12.1
OEL 5.4
Operations of requisition and PO doing im but I got error ORA-00976 LEVEL,PRIOR, or ROWNUM not allowed here in Package PO_DOCUMENT_CHECKS_PVT Procedure CHECK_REQUISITIONS. Solution 1. Please check HR Locations setup corresponding to location_id=XXX. For example the Country should be ‘KE’ instead of ‘Kenya’. Compare with that of location_id=YYY which is correct as per FND log. select location_id,location_code,country from hr_locations_all where location_id in (142,182); 2. After changing above setup, please run the SQL again to confirm the value. Note > ORA-00976 error while approving internal requisitions [968651.1 ID]
is the same thing with my mistake. But it is specific to the country in KENYA. 🙁
How can I get my FND journal so that I can see what location_id who got the error?
1* select location_id,location_code,country from hr_locations_all SQL> / LOCATION_ID LOCATION_CODE COUNTRY ———– ————- ——– censored Im selecting the table but it seems the location and country code are fine. Please help me resolve this one based on the notes above. Thanks a lot, Ms K Edited by: user_unlimited on Sep 16, 2010 10:28 PM Salvation; Please update thread when you did at the end of reading Respect of
HELIOS
키워드에 대한 정보 ora 00934 group function is not allowed here
다음은 Bing에서 ora 00934 group function is not allowed here 주제에 대한 검색 결과입니다. 필요한 경우 더 읽을 수 있습니다.
이 기사는 인터넷의 다양한 출처에서 편집되었습니다. 이 기사가 유용했기를 바랍니다. 이 기사가 유용하다고 생각되면 공유하십시오. 매우 감사합니다!
사람들이 주제에 대해 자주 검색하는 키워드 Oracle SQL Throwing \”ORA-00934: group function is not allowed here\” error for simple Select w/AVG
- answer
- oracle
- question
Oracle #SQL #Throwing #\”ORA-00934: #group #function #is #not #allowed #here\” #error #for #simple #Select #w/AVG
YouTube에서 ora 00934 group function is not allowed here 주제의 다른 동영상 보기
주제에 대한 기사를 시청해 주셔서 감사합니다 Oracle SQL Throwing \”ORA-00934: group function is not allowed here\” error for simple Select w/AVG | ora 00934 group function is not allowed here, 이 기사가 유용하다고 생각되면 공유하십시오, 매우 감사합니다.
