CODEWITHSUNDEEP
databaseoracle-database
Ramon Saraiva
Ramon Saraiva

Reputation: 518

ORA-00937: Not a single-group group function - Query error

Error: ORA-00937: Not a single-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 ocurring 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 why is it happening?

Upvotes: 0

Views: 2101

Answers (3)

Anurag Choudhary
Anurag Choudhary

Reputation: 890

In merge statement, if you are getting this error than simple use the group by and it will resolve the issue.

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;

Upvotes: 0

Ben
Ben

Reputation: 52863

You may have fixed it with max but that's not why it's happening and is a little bit hacky. Your problem is that your sub-query, which translates into a single column is not an aggregate query, min, max, sum etc and so needs to be included in a group by clause. You fixed this by wrapping it in max as the maximum of a single value will always be constant.

However, as your sub-query is, itself, an analytic query and will only ever return one row the obvious thing to do is to use a cartesian join to add it to your query. In the explicit join syntax this is known as the 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
                      )

The cartesian join has a bad reputation as it multiples the number of rows on one side of the join by the number of rows on the other. It does, however, have it's uses, especially in this sort of case.

Upvotes: 4

DCookie
DCookie

Reputation: 43523

It's happening because the subquery itself is a scalar result, not a group function. As you have apparently found, you can fix it by substituting a group function that yields an equivalent result to your subquery.

Upvotes: 1

Related Questions