Oracle SQL Two Group function why can't be in the same select?

Question

I have two table, defined by this create statement

CREATE TABLE PROJECT (project_name varchar(255), project_budget int, dedicated_project_leader int, dedicated_lead_developer int, dedicated_lead_consultant int, company_id int, project_id int, PRIMARY KEY (project_id));
CREATE TABLE WORKER (worker_name varchar(255), worker_salary int, worker_anual_bonus int, worker_type varchar(255), leader int, company_id int, project_id int, worker_id int, PRIMARY KEY (worker_id));

I'd like to ask for the sum of the budgets of the project than divide it by the number of the workers---)

SELECT (SUM(p.project_budget) / (SELECT count(WORKER.WORKER_NAME) from WORKER)) as AVG_BONUS
  FROM PROJECT p;

Unfortunateley this raise an "ORA--00937:not a single-group group function" exception.

However if i execute this query:

SELECT count(WORKER.WORKER_NAME) from WORKER)

The result will be:

And if i divide the summa of budgets with the number instead of the result:

SELECT (SUM(p.project_budget) / 16) as AVG_BONUS
      FROM PROJECT p;

I also got a perfect result e.g.:

294117,647058823

The solution is that i need to group by some id defined at the project table, but don't think it is logical....

SELECT (SUM(p.project_budget) / (SELECT count(WORKER.WORKER_NAME) from WORKER)) as AVG_BONUS
  FROM PROJECT p
   GROUP BY p.company_id;

It is also working.

Is anyone has a suggestion why can't i use it? (I have a feeling it is only an sql language specification that there can not exist two group function at a select without group by...)

score 2 · Accepted Answer · answered Jan 21 '13 at 12:08

2

you have to push each aggregate in this case into its own non-dependant view..eg:

select budget/workers
  from (select (select sum(p.project_budget) from project p) budget,
               (select count(w.worker_name) from worker w) workers
          from dual);

answered Jan 21 '13 at 12:08

DazzaL

21,638
3
49
57

I've upvoted this answer... So dual seems transform/convert the result to a string and with this string finally i can divide the sum... Thanks. Question: So is there some rule at Oracle/SQL which says no two group function is allowed in a same select? – czupe Jan 21 '13 at 12:35
@czupe no, there's no such rule as you can have `select count(*), max(whatever), min(whatever)` etc in a single SQL. its just that in this case, without a group by , oracle doesn't realise that the nested select wouldn't be safe). another way of allowing this would be to group by a literal `SELECT (SUM(p.project_budget) / (SELECT count(WORKER.WORKER_NAME) from WORKER)) as AVG_BONUS from project p group by 1;` if you prefer that workaround :) – DazzaL Jan 21 '13 at 12:45
i think dual also a workaround : ) but thanks. i thought something similar "oracle doesn't realise ..." but cant express myself this clear. – czupe Jan 21 '13 at 12:56

score 1 · Answer 2 · answered Jan 21 '13 at 12:17

1

Or you could try a join:

select sum(p.project_budget),
count(w.worker_name),
sum(p.project_budget)/
count(w.worker_name) as avgbonus
from project p
join
worker w
on w.project_id = p.project_id
;

sqlfiddle demo

| SUM(P.PROJECT_BUDGET) | COUNT(W.WORKER_NAME) | AVGBONUS |
-----------------------------------------------------------
|                150000 |                    4 |    37500 |

answered Jan 21 '13 at 12:17

bonCodigo

14,268
1
48
91

def worth an upvote the mass of work you made dude with the sqlfiddle. Thanks... And this also seems OK. but only Dazzal give me some kind of explanation... – czupe Jan 21 '13 at 12:40
@czupe this is just another way of doing what you need as Dazzal already explain the reason. :) [Here is the main explanation of why your query failed.](http://stackoverflow.com/questions/2150511/how-to-resolve-ora-00937-not-a-single-group-group-function-when-calculating-per), that's an exact classic of what you tried out. – bonCodigo Jan 21 '13 at 12:51

score 1 · Answer 3 · answered Jan 21 '13 at 12:19

1

How about this query?

SELECT  sum(project_budget/AVG_BONUS) as AVG_BONUS from(
  SELECT p.project_budget, (SELECT count(WORKER.WORKER_NAME) from WORKER) as AVG_BONUS
  FROM PROJECT p
  )x

answered Jan 21 '13 at 12:19

TechDo

18,398
3
51
64

thanks, weird query, dunno if is Ok or not:) upvoted, i will check later, i hope – czupe Jan 21 '13 at 14:14

score 0 · Answer 4 · answered Jan 21 '13 at 11:54

0

I would do it this way:

SELECT (SUM(p.project_budget) / MAX(worker_count)) as AVG_BONUS
FROM 
  PROJECT p
  ,(SELECT count(WORKER.WORKER_NAME) AS worker_count  
    FROM WORKER) wc
GROUP BY p.company_id;

I think your way is not working because the sub query is neither an aggregate or a group.

answered Jan 21 '13 at 11:54

Dave Sexton

10,768
3
42
56

my query is working, but thanks, the question is related to why can't i execute without "group by" And your query is exactly is the same as mine:) but only extended with alias:) – czupe Jan 21 '13 at 11:57

score 0 · Answer 5 · answered Jan 21 '13 at 12:01

0

Try with soemthing like
select project_name, project_budget / case when w_cnt > 0 then w_cnt else 1 end as w_cnt from PROJECT p, ( select project_id, count(*) w_cnt from WORKER w group by project_id ) w where p.project_id = w.project_id

answered Jan 21 '13 at 12:01

igr

3,409
1
20
25

sorry, but this query is so hardcore for me:) i dont understand it, but thanks And also there is still a group by. – czupe Jan 21 '13 at 12:37

Oracle SQL Two Group function why can't be in the same select?

5 Answers5