Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Using grouping sets to accumulate values

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;
Best Answer
-
Hi, @BeefStu
I seemed to get most of it working except how to figure out the 'CNT'.l part, which is probably the most important.
You're off to a good start. The count part is just
COUNT (*)
.Here's one way to do it:
WITH got_mnth_yr AS ( SELECT e.employee_id, e.first_name, e.last_name , TO_CHAR (a.absent_date, 'MON') AS mnth , TO_CHAR (a.absent_date, 'MM') AS mnth_num , TO_CHAR (a.absent_date, 'YYYY') AS yr FROM employees e JOIN absences a ON a.employee_id = e.employee_id ) SELECT yr , CASE WHEN GROUPING (mnth_num) = 0 THEN MIN (mnth) END AS month , employee_id , CASE WHEN GROUPING (employee_id) = 0 THEN first_name WHEN GROUPING (mnth_num) = 0 THEN 'Total for Month' WHEN GROUPING (yr) = 0 THEN 'Total for Year' ELSE 'Grand Total' END AS last_name , last_name , COUNT (*) AS cnt FROM got_mnth_yr GROUP by GROUPING SETS ( (yr, mnth_num, employee_id, last_name, first_name) , (yr, mnth_num) , (yr) , ( ) ) ORDER BY yr, mnth_num, employee_id ;
You don't absolutely need a sub-query; instead of using yr and mnth, you could use the corresponding TO_CHAR expressions everywhere. (Oracle is smart enough to evaluate each distinct expression only once.) However, I find it easier to read and understand (and therefore to debug and maintain) to use short, mnemonic names as much as possible.
For most jobs, GROUPING is more useful than GROUPING_ID.
'JAN', 'FEB', ..., 'DEC' are no good for sorting. The query above includes both the month abbreviation you want to display and the month number for sorting, but it uses only the month_num in the GROUP BY and ORDER BY clauses.
If you think there is a better or more effective way to accomplish this task please let me know.
You had the right idea: I think GROUPING SETS is the best tool for this task. ROLLUP would work, also.
Answers
-
Hi, @BeefStu
I seemed to get most of it working except how to figure out the 'CNT'.l part, which is probably the most important.
You're off to a good start. The count part is just
COUNT (*)
.Here's one way to do it:
WITH got_mnth_yr AS ( SELECT e.employee_id, e.first_name, e.last_name , TO_CHAR (a.absent_date, 'MON') AS mnth , TO_CHAR (a.absent_date, 'MM') AS mnth_num , TO_CHAR (a.absent_date, 'YYYY') AS yr FROM employees e JOIN absences a ON a.employee_id = e.employee_id ) SELECT yr , CASE WHEN GROUPING (mnth_num) = 0 THEN MIN (mnth) END AS month , employee_id , CASE WHEN GROUPING (employee_id) = 0 THEN first_name WHEN GROUPING (mnth_num) = 0 THEN 'Total for Month' WHEN GROUPING (yr) = 0 THEN 'Total for Year' ELSE 'Grand Total' END AS last_name , last_name , COUNT (*) AS cnt FROM got_mnth_yr GROUP by GROUPING SETS ( (yr, mnth_num, employee_id, last_name, first_name) , (yr, mnth_num) , (yr) , ( ) ) ORDER BY yr, mnth_num, employee_id ;
You don't absolutely need a sub-query; instead of using yr and mnth, you could use the corresponding TO_CHAR expressions everywhere. (Oracle is smart enough to evaluate each distinct expression only once.) However, I find it easier to read and understand (and therefore to debug and maintain) to use short, mnemonic names as much as possible.
For most jobs, GROUPING is more useful than GROUPING_ID.
'JAN', 'FEB', ..., 'DEC' are no good for sorting. The query above includes both the month abbreviation you want to display and the month number for sorting, but it uses only the month_num in the GROUP BY and ORDER BY clauses.
If you think there is a better or more effective way to accomplish this task please let me know.
You had the right idea: I think GROUPING SETS is the best tool for this task. ROLLUP would work, also.
-
Frank, once again thanks its perfect. I knew I needed a count(*) but I was unsure how to do it on each group, nice trick you used with the month number.
I'm not sold on the 'MON' vs month number. 'MON' was the first thing I thought off. I actually prefer the number and will use it instead.
Happy to report everyone was ecstatic with the several options, which were provided and the extra effort.Thank you and the rest of the community for help and sharing knowledge. I'm hoping I don't get pulled into development group too LOL. It's time for a cold beer !!!!