Not a single function error in Oracle

Question

SQL query:

      SELECT SUM (gross_amount) /
           (SELECT TO_CHAR (
                  LAST_DAY (TO_DATE (MAX (accounting_period), 'YYYYMM')),
                 'DD') 
           FROM t_beta_invoice)
       FROM t_beta_invoice_details

I tried to calculate the daily invoice amount by dividing the total amount to the number of the days of given month in table t_beta_invoice. Firstly, i get the accounting period, which is in format of 'YYYYMM' and find the last day of it to calculate whether it contains 28,29,30 or 31 days.

But whenever i tried to run this , it gives me *ORA-00937: not a single-group group function*. Can anyone help about this situation?

Is there referential integrity (primary/foreign key )between `t_beta_invoice` and `t_beta_invoice_details` tables? — Nick Krasnov, Aug 13 '13 at 13:40
Well you appear to be using a character string as the denominator -- might like to change that just for the sake of good form. — David Aldridge, Aug 13 '13 at 21:18

score 2 · Answer 1 · answered Aug 13 '13 at 11:58

2

Try to use this and make sure the second query doesn't return 0.

select

(SELECT SUM (gross_amount)      
           FROM t_beta_invoice_details)
 /

 (SELECT TO_CHAR (LAST_DAY (TO_DATE (MAX (accounting_period), 'YYYYMM')), 'DD') 
           FROM t_beta_invoice)
FROM DUAL;

answered Aug 13 '13 at 11:58

valex

23,966
7
43
60

thanks for every comment, this one is the most effective one. – onurburak9 Aug 13 '13 at 14:57

score 2 · Answer 2 · answered Aug 13 '13 at 14:10

For the record, the issue here is that you have a group function and a non-group function (the subquery) in the SELECT list.

You can use group functions without a group by: e.g. select count(*), sum(object_id) from all_objects works fine but as soon as you include a non-group function, you need to have it in a group by:

No good: select object_type, count(*), sum(object_id) from all_objects

ORA-00937: not a single-group group function

Works fine:

select object_type, count(*), sum(object_id) group by object_type

So in your query the subquery is the problem. The catch is that you can't have GROUP BY (subquery) as you'll get ORA-22818: subquery expressions not allowed here.

but (at least in 11g) here's an interesting twist: if you add GROUP BY (some constant) you can get it to work:

e.g. select (select dummy from dual) as test, sum(1) from dual

ORA-00937: not a single-group group function

but

select (select dummy from dual) as test, sum(1) from dual group by 'huh?'

works.

Otherwise you have to separate the SUM query from the "accounting period" subquery as was suggested by valex.

Lukas Vermeer · Answer 3 · 2013-08-14T07:10:14.370

You are getting an ORA-00937 because "A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause."

One possible (ugly) solution would be delegating the SUM to a subquery.

SELECT 
    (SELECT SUM(gross_amount) FROM t_beta_invoice_details) /
    (SELECT TO_CHAR(LAST_DAY(TO_DATE(MAX(accounting_period),'YYYYMM')),'DD') 
     FROM t_beta_invoice)
FROM DUAL;

Perhaps more elegant (but not much) would be using a JOIN.

SELECT SUM (t.gross_amount) / x.days
FROM t_beta_invoice_details t,
(SELECT TO_CHAR(LAST_DAY(TO_DATE(MAX(accounting_period),'YYYYMM')),'DD') as days
 FROM t_beta_invoice) x
GROUP BY x.days;

Hope that helps.

score -1 · Answer 4 · answered Aug 13 '13 at 11:57

-1

'ORA-00937: not a single-group group function'

This error comes if Aggregate function like MAX,MIN,SUM etc is used without using GROUP BY clause.

answered Aug 13 '13 at 11:57

Mudassir Hasan

28,083
20
99
133

1

No, it comes about if a non-aggregate expression is used in a SELECT (or ORDER BY I suppose) clause along with an aggregate function. – David Aldridge Aug 13 '13 at 21:25

Not a single function error in Oracle

4 Answers4