Using grouping sets to accumulate values

BeefStu

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;

Frank Kulash

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 Kulash

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.

BeefStu

@Frank Kulash

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 !!!!