## Using grouping sets to accumulate values

Jul 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
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
``````

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