CODEWITHSUNDEEP
sqloracle-databaseora-00937
onurburak9
onurburak9

Reputation: 43

Not a single function error in Oracle

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?

Upvotes: 2

Views: 273

Answers (4)

Lukas Vermeer
Lukas Vermeer

Reputation: 5940

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.

Upvotes: 0

Patrick Marchand
Patrick Marchand

Reputation: 3445

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.

Upvotes: 2

valex
valex

Reputation: 24144

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;

Upvotes: 2

Mudassir Hasan
Mudassir Hasan

Reputation: 28761

'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.

Upvotes: -1

Related Questions