ORA-00937:不是单组分组功能-查询错误 [英] ORA-00937: Not a single-group group function - Query error

问题描述

错误:ORA-00937:不是单组分组功能

查询:

    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)

这里发生错误:

(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?

推荐答案

您可能已使用max对其进行了修复，但这不是为什么的事情，并且有点怪异.您的问题是，转换为单列的子查询不是聚合查询，min，max，sum等，因此需要包含在group by子句中.您通过将其包装在max中来解决此问题，因为单个值的最大值始终是恒定的.

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.

但是，由于子查询本身就是一个分析查询，因此只会返回一行，显然要做的事情是使用笛卡尔联接将其添加到查询中.在显式联接语法中，这称为 cross join . /p>

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.

这篇关于ORA-00937:不是单组分组功能-查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！