Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Using grouping sets to accumulate values

BeefStuJul 23 2021

I'm trying to use grouping sets
to find the total count of absences for (employee_id, month, year), (month,year), (year), Grand Total of all absences.

I seemed to get most of it working except how to figure out the 'CNT'.l part, which is probably the most important.

Below is the output I am expecting based on a test case I have provided. I've kept it simple by only providing 10 rows in the absences table.

I'm not married to the idea of using grouping sets. If you think there is a better or more effective way to accomplish this task please let me know. Thanks in advance to all that answer.

YEAR MONTH EMPLOYEE_ID FIRST_NAME LAST_NAME CNT

2021 JUL 1 Jane Doe 5
2021 JUL 4 MIke Jones 4

Month Total 9

2021 AUG 3 Justin Case 1
Month Total 1

Year Total 10
Grand Total 10

Create table employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
last_name VARCHAR2(20),
card_num VARCHAR2(10),
work_days VARCHAR2(7)
);

 ALTER TABLE employees  
         ADD ( CONSTRAINT employees\_pk  
       PRIMARY KEY (employee\_id));  

INSERT INTO employees                     
(  
EMPLOYEE\_ID,  
first\_name,   
last\_name,  
card\_num,  
work\_days  
)  
WITH names AS (   
SELECT 1, 'Jane',     'Doe','F123456', 'NYYYYYN'FROM dual UNION ALL   
SELECT 2, 'Madison', 'Smith','R33432','NYYYYYN'  

FROM dual UNION ALL
SELECT 3, 'Justin', 'Case','C765341','NYYYYYN'
FROM dual UNION ALL
SELECT 4, 'Mike', 'Jones','D564311','NYYYYYN' FROM dual )
SELECT * FROM names;

 create table absences(  
 seq\_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,  
 employee\_id NUMBER(6),  
 absent\_date DATE,  
 constraint absence\_chk check (absent\_date=trunc(absent\_date, 'dd')),  
 constraint absence\_pk primary key (employee\_id, absent\_date)  
  );  

begin    
  insert into absences values (1,1, date'2021-07-21');    
  insert into absences values (2,4, date'2021-07-22');    
  insert into absences values (3,4, date'2021-07-23');    
  insert into absences values (4,4, date'2021-07-26');    
  insert into absences values (5,1, date'2021-07-30');    
  insert into absences values (6,1, date'2021-07-31');    
  insert into absences values (7,4, date'2021-07-13');    
  insert into absences values (8,1, date'2021-07-14');    
  insert into absences values (9,1, date'2021-07-15');   
  insert into absences values (10,3, date'2021-08-05');    
  commit;    
end;  

select
to_char(a.absent_date, 'YYYY') year,
to_char(a.absent_date, 'MON') month,
e.employee_id,
e.first_name,
e.last_name, grouping_id(to_char(a.absent_date, 'YYYY'), to_char(a.absent_date, 'MON'), e.employee_id, e.first_name, e.last_name) the_grouping_id
from employees e
join absences a
on a.employee_id = e.employee_id
group by grouping sets
(
(to_char(a.absent_date, 'YYYY'),
to_char(a.absent_date, 'MON'), e.employee_id, e.first_name, e.last_name),
(to_char(a.absent_date, 'YYYY'),
to_char(a.absent_date, 'MON')),
(to_char(a.absent_date, 'YYYY')),
()
)
order by year, month, employee_id;

This post has been answered by Frank Kulash on Jul 23 2021
Jump to Answer
Comments
Post Details
Added on Jul 23 2021
2 comments
510 views