Gathering counts in grouping sets

Question

I read about grouping sets and I would like to gather counts and totals on groups (A,B,C CNT column), (A,B), (A)

Below is my test CASE and expected results. I'm unsure how to generate the CNT column as part of my output. I suspect it's probably a count(*) but I am unsure how to incorporate it with each group. Below are my expected results

Please note though the dates are in full 'DD-MON-YYYY HH24:MI:SS' because of my NLS_DATE_FORMAT I only want to group on 'DD-MON-YYYY ' part and don't want the time as part of the output. Having my users enter another NLS_DATE_FORMAT for a SESSION will lead to foreseeable problems.

In addition, I want to include the text after each 'Total'

I'm not married to the idea of grouping sets but it looked the simplest to implement. I also stumbled upon a ROLLUP command, would that be a better approach?

Thanks in advance for your time, patience and expertise.

    My expected results 

     A       B C   CNT  
    20210805 1 1  2  
    20210805 1 2  1 
    20210805 2 1  2
    20210805 2 2  1
 Total AB         6 
   20210806  1 1  2  
   20210806  1 2  1 
   20210806  2 1  2
   20210806  2 2  1
 Total AB         6
 Total A         12
    
    ALTER SESSION SET 
       NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

    CREATE table abc(
          a DATE,
          b NUMBER(4),
          c NUMBER(4)
     );

    INSERT into abc (a,b,c) VALUES     
  (to_date('05-AUG-2021 10:00:00','DD-MON-YYYY HH24:MI:SS'), 1,1);

    INSERT into abc (a,b,c) VALUES     
  (to_date('05-AUG-2021 10:00:00','DD-MON-YYYY HH24:MI:SS'), 1,1);

  INSERT into abc (a,b,c) VALUES     
  (to_date('05-AUG-2021 10:00:00','DD-MON-YYYY HH24:MI:SS'), 1,2);

  INSERT into abc (a,b,c) VALUES     
  (to_date('05-AUG-2021 10:00:00','DD-MON-YYYY HH24:MI:SS'), 2,1);

  INSERT into abc (a,b,c) VALUES     
  (to_date('05-AUG-2021 10:00:00','DD-MON-YYYY HH24:MI:SS'), 2,1);

  INSERT into abc (a,b,c) VALUES     
  (to_date('05-AUG-2021 10:00:00','DD-MON-YYYY HH24:MI:SS'), 2,2);

  INSERT into abc (a,b,c) VALUES     
  (to_date('06-AUG-2021 10:00:00','DD-MON-YYYY HH24:MI:SS'), 1,1);

  INSERT into abc (a,b,c) VALUES     
  (to_date('06-AUG-2021 10:00:00','DD-MON-YYYY HH24:MI:SS'), 1,1);

  INSERT into abc (a,b,c) VALUES     
  (to_date('06-AUG-2021 10:00:00','DD-MON-YYYY HH24:MI:SS'), 1,2);

  INSERT into abc (a,b,c) VALUES     
  (to_date('06-AUG-2021 10:00:00','DD-MON-YYYY HH24:MI:SS'), 2,1);

  INSERT into abc (a,b,c) VALUES     
  (to_date('06-AUG-2021 10:00:00','DD-MON-YYYY HH24:MI:SS'), 2,1);

  INSERT into abc (a,b,c) VALUES     
  (to_date('06-AUG-2021 10:00:00','DD-MON-YYYY HH24:MI:SS'), 2,2);

   SELECT
      TRUNC(a),b,c,  
grouping_id(a,b,c) the_grouping_id

   FROM abc
    group by grouping sets
    (
        (a,b,c),
        (a,b),
        (a)
    )
   order by a,b,c;

Answer 1

You're grouping sets do not match your example output.

You have subtotals under each day, that's the set (A) only.

Then you have an overall subtotal, that's the set ().

You indeed do need a COUNT(*), which is just added as another column to the SELECT clause.

You don't appear to have any need for the grouping id.

And your table has A as a DATE datatype, so the values are truncated when inserted, no need to do so at run time.

So...

SELECT
  A, B, C, COUNT(*)
FROM
  abc
GROUP BY
  GROUPING SETS (
    (A,B,C),
    (A),
    ()
  )
ORDER BY
  A, B, C

EDIT: As per comments, TRUNC() is indeed needed.

I'd use a sub-query or CTE to apply it just once.

• https://dbfiddle.uk/?rdbms=oracle_18&fiddle=65adc2bfa61bc9e7acabbf2b4db645f6

Which gives...

SELECT
  A, B, C, COUNT(*)
FROM
(
  SELECT TRUNC(A) A, B, C FROM abc
)
  abc_trunc
GROUP BY
  GROUPING SETS (
    (A,B,C),
    (A),
    ()
  )
ORDER BY
  A, B, C

Answer 2

To generate row headers

   SELECT  a,b,c, count(*) n,
     case grouping_id(a,b,c) when 7 then 'Grand total'
                             when 3 then 'Total A' 
                             when 1 then 'Total AB' end row_header         
   FROM abc
   GROUP BY 
    GROUPING SETS (
        (a,b,c),
        (a,b),
        (a), 
        ()
    )
   ORDER BY a,b,c;

If you need to truncate your real column, use TRUNC consistently in the query

   SELECT  trunc(a) x, b,c, count(*) n,
     case grouping_id(trunc(a),b,c) when 7 then 'Grand total'
                             when 3 then 'Total A' 
                             when 1 then 'Total AB' end row_header         
   FROM abc
   GROUP BY 
    GROUPING SETS (
        (trunc(a),b,c),
        (trunc(a),b),
        (trunc(a)), 
        ()
    )
   ORDER BY trunc(a),b,c;