7

can someone explain me why this query requires GROUP BY clause in Oracle while is perfectly fine in MSSQL, MySQL, Postgresql?

select count(*) / (select count(*) from dual)from dual

Oracle complains 

"[Error] Execution (1: 27): ORA-00937: not a single-group group function"

If I change it and add dummy group by like this

select count(*) / (select count(*) from dual)from dual
group by null

then it's fine for Oracle. Why Oracle requires this group by?

Shadow
  • 33,525
  • 10
  • 51
  • 64
olek
  • 83
  • 5
  • Can you please provide the real query, your example does not make any sense. – Wernfried Domscheit Aug 13 '17 at 20:19
  • I provided this one to make it as abstract as it can be but of course here is one from Northwind DB. select sum(UnitPrice * Quantity) / (select count(*) from Orders) from OrderDetails – olek Aug 13 '17 at 20:24
  • @WernfriedDomscheit - If change `dual` with any real table(s) , result will be same – Oto Shavadze Aug 13 '17 at 20:27
  • This is not answer to question, but if use second sub-query also, then should work: `select (select count(*) from dual ) / (select count(*) from dual ) from dual` – Oto Shavadze Aug 13 '17 at 20:28
  • Thanks very much. I know some more different ways how to force Oracle to calculate this :) but i'm just courious is there any reason why it is not working in Oracle? I was doing some presentation and usually I use MSSQL for this but this time i had Oracle. I was 100% sure that it will work and you can imagine my surprise when i saw this "not a single-group group function" message :) – olek Aug 13 '17 at 20:36
  • 3
    Possible duplicate of [ORA-00937: Not a single-group group function - Query error](https://stackoverflow.com/questions/11349571/ora-00937-not-a-single-group-group-function-query-error) – trincot Aug 13 '17 at 21:03
  • I think here it is beautifully explained: https://stackoverflow.com/questions/1795198/sql-not-a-single-group-group-function – Alex Dec 21 '17 at 13:55

3 Answers3

1

The format of your query is -

SELECT aggregate_function()/(subquery) FROM table;

While parsing the query for the syntax check, Oracle can not tell whether the subquery is a correlated subquery or a non-correlated subquery.

If it is a correlated subquery then the result of the subquery will be dependent on each row of the 'table' i.e. the result of the subquery may be different for each record.

For the sake of understanding, consider the subquery as a variable.
Since the aggregate_function is used along with a variable, the value of which may differ for each record - Oracle expects the query to have a group by clause.

Example

Table: tab1

c1 | c2

A1 | 1

A2 | 1

B1 | 2

C1 | 3

Table: tab2

c3 | c4

X1 | 1

X2 | 2

Y1 | 1

Z1 | 1

Query:

_SELECT count(*)/(SELECT count(*) FROM tab2 t2 WHERE t2.c4 <= t1.c2) FROM tab1 t1;_

It wouldn't work. I hope you agree.

Why it may work in MSSQL, MySQL or Postgresql - I am not sure. Probably they are pro-actively checking whether the subquery is a correlated subquery or a non-correlated subquery. If it is not a correlated subquery, they are allowing the execution.

Harun Diluka Heshan
  • 1,155
  • 2
  • 18
  • 30
Nimit Rastogi
  • 51
  • 4
0

MySQL has some syntax which are not valid in ANSI SQL. basically, here, you have a count(*) which is an aggregation function. Alone, it doesn't require a group by but if you add select count(*) which may depend on your line - it is not the case here, but you could make a join internally - then your second select is not a constant, and you should us group by.

Steven
  • 14,048
  • 6
  • 38
  • 73
0

As far as i know, Your first query:

select count(*) / (select count(*) from dual)from dual

Sql will understand Count() as a column and (select count() from dual) as a second column => You must group by second column.

For example:

select count(*), (select count(1) from dual)from dual

It will throw the same error. But if you group by the second column it will be true. Example

select count(*), (select count(1) from dual)from dual
group by 1
Hong Van Vit
  • 2,884
  • 3
  • 18
  • 43