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;