Geek Answers Handbook

ORA-00937: Non-group group function - request error

Error: ORA-00937: not a group group function

Query:

select count(*) todas, sum(case when i.prioridade = 1 then 1 else 0 end) urgente, sum(case when i.prioridade = 2 then 1 else 0 end) alta, sum(case when i.prioridade = 3 then 1 else 0 end) normal, sum(case when i.prioridade = 4 then 1 else 0 end) baixa, (select count(*) from GMITEMOS i inner join GMCTLSLA c on c.os = i.cd_numero_os and c.item = i.item where i.situacao in ('A', 'I', 'P') and c.ordem = 99999 ) naoAvaliados, sum(case when i.situacao = 'P' then 1 else 0 end) pendentes, sum(case when i.situacao = 'A' or i.situacao = 'I' then 1 else 0 end) iniciados from GMITEMOS i where i.situacao in ('A', 'I', 'P') and exists (select 1 from GMCTLSLA c where c.os = i.cd_numero_os and c.item = i.item)

The error is here:

 (select count(*) from GMITEMOS i inner join GMCTLSLA c on c.os = i.cd_numero_os and c.item = i.item where i.situacao in ('A', 'I', 'P') and c.ordem = 99999 ) naoAvaliados

Can someone tell me why this is happening?

0
database oracle
source share
3 answers

You may have installed it with max , but that’s not why this happens, and is a bit hacked. Your problem is that your subquery, which translates into a single column, is not an aggregate query, min , max , sum , etc. And therefore it should be included in the group by clause. You fixed this by wrapping it in max , since the maximum of one value will always be constant.

However, since your subquery is itself an analytic query and will only ever return a single row, the obvious thing to do is use a Cartesian join to add it to your query. In explicit join syntax, this is called cross join .

 select count(*) todas , sum(case when i.prioridade = 1 then 1 else 0 end) urgente , sum(case when i.prioridade = 2 then 1 else 0 end) alta , sum(case when i.prioridade = 3 then 1 else 0 end) normal , sum(case when i.prioridade = 4 then 1 else 0 end) baixa , naoAvaliados , sum(case when i.situacao = 'P' then 1 else 0 end) pendentes , sum(case when i.situacao = 'A' or i.situacao = 'I' then 1 else 0 end) iniciados from GMITEMOS i cross join (select count(*) as naoAvaliados from GMITEMOS j inner join GMCTLSLA k on k.os = j.cd_numero_os and k.item = j.item where j.situacao in ('A', 'I', 'P') and k.ordem = 99999 ) where i.situacao in ('A', 'I', 'P') and exists (select 1 from GMCTLSLA c where c.os = i.cd_numero_os and c.item = i.item )

A Cartesian compound has a bad reputation because it multiplies the number of rows on one side of the connection by the number of rows on the other. However, he uses it, especially in this case.

+4
source share

This is because the subquery itself is a scalar result, not a group function. As you seem to have discovered, you can fix this by replacing the group function, which gives an equivalent result to your subquery.

+1
source share

In merging, if you get this error, than just use a group and it will solve the problem.

 merge into table1 tb1 using (select a.id,a.ac_no,sum(a.qy) as qyt,sum(a.amt) as sum_amt from table2 a, table1 b where a.id=b.id and a.id = '1234' and a.date = '08Oct2014' and a.ac_no in (123, 234, 345) and a.ac_no = b.ac_no group by a.ac_no,a.id )qry on (qry.id=tb1.id and qry.ac_no=tb1.ac_no ) when matched then update set qy=qry.qy,amt = qry.sum_amt;
0
source share

More articles:


All Articles