CODEWITHSUNDEEP
sqloracle-database
J.Doe
J.Doe

Reputation: 105

Sum of count in Oracle

I'm beginner in Oracle and I was wondering why I cannot make sum out of count function. Here's the example of my queries and tries:

select job_id, SUM(count(job_id)) as "jobs" 
from employees 
group by job_id

error:

ORA-00937: not a single-group group function

I also tried to use cte but to my surprise I can't: 

with cte1 as (
  select job_id, count(job_id) as "jobs" 
  from employees 
  group by job_id
)
select sum(jobs) from cte1

ORA-00904: "JOBS": invalid identifier

Upvotes: 0

Views: 3782

Answers (2)

Chris Saxon
Chris Saxon

Reputation: 9765

You can nest aggregates. But you need to remove the columns you're grouping by from the select list:

select sum(count(*)) as "jobs" 
from   hr.employees 
group  by job_id;

jobs   
   107

This counts the number of rows for each job. And then sums these up.

Which is the same as:

with jobs as ( 
  select count(*) c
  from   hr.employees 
  group  by job_id
)
  select sum ( c ) from jobs;

SUM(C)   
     107

Your CTE is failing because you've quoted "jobs". This makes it a lowercase identifier you need to reference in quotes to access:

with cte1 as(
  select job_id,
         count( job_id ) as "jobs"
  from   hr.employees
  group by job_id
)
  select sum( "jobs" )
  from   cte1;

SUM("JOBS")   
          107

Upvotes: 3

Ori Marko
Ori Marko

Reputation: 58774

you don't need the redundant sum, you already have count of rows per job_id:

select job_id, count(job_id) as "jobs" from employees group by job_id

Upvotes: 1

Related Questions