CODEWITHSUNDEEP
sqloracle-database
Electrob
Electrob

Reputation: 49

Error 00937 "not a single-group group function"

i face ORA-00937: "not a single-group group function" error problem with this sql query: Any ideas please ! Thanks.

SELECT avg(count(*)) as value, 'Taux remplissage' as serie, to_char(c.datcre, 'YYYY-MM-DD') as arg
from charge c left join emplac e ON c.adr = e.adr 
where e.ADR is not null and e.empsta != 'I' and e.empsta != 'V'
and (trunc(c.datcre) >= to_date('2020-11-01','YYYY-MM-DD'))
and (trunc(c.datcre) <= to_date('2021-11-30','YYYY-MM-DD'))
GROUP BY to_char(c.datcre, 'YYYY-MM-DD')
ORDER BY arg, serie

Upvotes: 0

Views: 81

Answers (2)

Electrob
Electrob

Reputation: 49

SELECT count(*) * 100.0 / (select count(*) from emplac) as value,'% remplissage ' || c.mag as serie, to_char(c.datcre, 'YYYY-MM-DD') as arg
from charge c left join emplac e ON c.adr = e.adr 
where e.ADR is not null and e.empsta != 'I'
and (trunc(c.datcre) >= to_date('2020-11-01','YYYY-MM-DD'))
and (trunc(c.datcre) <= to_date('2021-11-30','YYYY-MM-DD'))
GROUP BY to_char(c.datcre, 'YYYY-MM-DD'), c.mag
ORDER BY arg

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

I'm not sure what you mean by avg(count(*)) in this context. This would be used (in Oracle) in a context where you want a result set with one row. But the order by suggests that you are expecting multiple rows.

If you just want the count(*) then you would use:

select count(*) as value, 'Taux remplissage' as serie, 
       to_char(c.datcre, 'YYYY-MM-DD') as arg
from charge c join
     emplac e 
     ON c.adr = e.adr 
where e.ADR is not null and e.empsta not in ('I', 'V') and
      c.datcre >= date '2020-11-01' and
      c.datcre < date '2021-12-01'
group by to_char(c.datcre, 'YYYY-MM-DD')
order by arg;

If you want the overall average on every row, you would use:

avg(count(*)) over () as average

Note the other changes to the query:

  • not in is much simpler than a chain of <>s.
  • Strictly speaking, the is not null is redundant, but I left it in.
  • The where clause turns the left join into an inner join anyway, so you should specify the join you are actually using.
  • Oracle supports the Standard SQL syntax for date constants. You might as well use it.
  • The date comparisons are generally going to be more efficient when you remove functions on the column. That helps the optimizer.

Upvotes: 2

Related Questions