15447

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

Question:

<strong>Error: ORA-00937: Not a single-group group function</strong>

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?

Answer1:

You may have fixed it with max but that's not <em>why</em> 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 <a href="http://en.wikipedia.org/wiki/Join_%28SQL%29#Cross_join" rel="nofollow">cross join</a>.

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.

Answer2:

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.

Answer3:

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;

Recommend

  • Sockets asynchronous in C# without loopback
  • Transition height onload of d3js rect svg
  • @Size annotation not recognized (JPA)
  • How can I replace all key fields in a string with replacement values from a table in T-SQL
  • interpolation in 3d computer graphics
  • How to assign value to angular pagination custom template?
  • Merge several columns into one with specific conditions in R
  • Getting the min and max values from a PHP array
  • Getting wrong values for other columns when I select MAX(updated_date)
  • How to deal with SpiderWebPlot in JFreeChart?
  • Python: how to split and return a list from a function to avoid memory error
  • How to select all column in XML
  • Facebook PHP SDK 3 won't work on 32-bit system, facebook ids treated as int and can be > 214
  • WPF: Resize item size to have all items visible
  • Multiarray with one entry [duplicate]
  • how to bypass pinentry (passphrase screen) while decrypting a file using gpgme
  • jqPlot date axis - bars drawn on wrong day?
  • How to add specific media query rule when media query combined
  • How to merge two Request in Laravel
  • Stitching 2 images (OpenCV)
  • Reading space separated values file in c++ error
  • Google analytics measurement protocol session timeout and query time limits
  • Double dispatch in Java example
  • How to convert integer to string and get length of string
  • how to get username into sql trigger when multiple users signed on from asp membership
  • RxJava debounce by arbitrary value
  • D3 get axis values on zoom event
  • C: Incompatible pointer type initializing
  • Allowing both email and username for authentication
  • $wpdb not working in file of WordPress plugin
  • Get one-time binding to work for ng-if
  • RectangularRangeIndicator format like triangular using dojo
  • Why HTML5 Canvas with a larger size stretch a drawn line?
  • TFS: Get latest causes slow project reloading
  • Controls, properties, events and timers running in design time
  • How do you troubleshoot character encoding problems?
  • How to get next/previous record number?
  • How do I rollback to a specific git commit
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • Django query for large number of relationships