Skip to Main Content

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
50 views