Sum of count in Oracle

Question

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

The CTE doesn't work because you have used a lower-case identifier in double-quotes but you don't include the double-quotes when you referenced the CTE in the main query. [Find out more](https://stackoverflow.com/a/6030439/146325).. — APC, Nov 15 '18 at 15:22

score 3 · Accepted Answer · answered Nov 15 '18 at 15:19

3

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

answered Nov 15 '18 at 15:19

Chris Saxon

9,105
1
26
42

Thank you so much, but what's the difference between alias in quotation marks and without it. I thought I have to put it in quotation marks. Now I'm confused where I have to use them (quotation marks) and where I don't – J.Doe Nov 15 '18 at 15:32
Quoting identifiers make them case sensitive. But you only have to use them in Oracle Database to access lowercase identifiers. So it's generally easier to avoid quoting names – Chris Saxon Nov 15 '18 at 15:37
So there is no situations in Oracle db when i have to use quoting? I was working in ms sql before and there was plenty of them – J.Doe Nov 16 '18 at 10:40
If you want to access a lowercase name, you have to use quotes. Same if you want to use characters other than alphanumeric, _, $ or #. It's rare you'll encounter this though. Provided you're not quoting identifiers ;) – Chris Saxon Nov 16 '18 at 15:36
Ok, and what about apostrophe? I see that it's been used in where statement and where else? – J.Doe Nov 21 '18 at 08:58
I'm not sure what you mean. If you want an apostrophe in a table/column name, you need to double quote it. Values between two single quotes are text literals. – Chris Saxon Nov 26 '18 at 09:48

score 1 · Answer 2 · answered Nov 15 '18 at 14:49

1

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

answered Nov 15 '18 at 14:49

Ori Marko

56,308
23
131
233

Sum of count in Oracle

2 Answers2