Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 108
  • Last Modified:

oracle rollup query

Hi ,

My question might sound easy , but I want to understand why oracle is not generating a grand total when I fire the below query

Select department_id , job_id , count(*)
from employees
group by department_id,rollup(department_id, job_id)

I get the grandtotal with rollup , if i fire the below query

Select department_id , job_id , count(*)
from employees
group by rollup(department_id, job_id)

Any help is really appreciated. The above queries are firedin HR schema.
0
sam_2012
Asked:
sam_2012
1 Solution
 
sdstuberCommented:
rollup()   applies the aggregates within the grouping.

In the first query you have grouped within department, so the rollup only generates counts within department.  The total within a department across all departments is still just the department.

That's why each department has 2 rows with a null job_id.   The second row is the grand total.

In the second query, you don't have a grouping other than the rollup itself so it generates totals on departments and across all departments.
1
 
Geert GruwezOracle dbaCommented:
well, in query1 you first group by department_id
that has priority over the rollup

that's why you don't get a total
0
 
sam_2012Author Commented:
awesome
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now