countpivot

How to use Count in a Pivot clause


I'm using Oracle 21C.
My query involves three tables that can be created with the following code:

Create Table vol_att
( prim_key integer NOT NULL,
 event_fkey integer,
 contact_fkey integer,
 attendance_type varchar2(256),
 CONSTRAINT vol_att_pk PRIMARY KEY (prim_key)
);
Insert All
   Into vol_att(prim_key, event_fkey, contact_fkey, attendance_type)
   Values(1, 1, 801, 'I')
   Into vol_att(prim_key, event_fkey, contact_fkey, attendance_type)
   Values(2, 1, 234, 'Z')
   Into vol_att(prim_key, event_fkey, contact_fkey, attendance_type)
   Values(3, 2, 258, 'I')
   Into vol_att(prim_key, event_fkey, contact_fkey, attendance_type)
   Values(4, 2, 234, 'Z')
   Select 1 from DUAL;
   
Create Table vol_con
( prim_key integer NOT NULL,
 last_name varchar2(256),
 first_name varchar2(256),
 CONSTRAINT vol_con_pk PRIMARY KEY (prim_key)
);
Insert All
   Into vol_con(prim_key, last_name, first_name)
   Values(234, 'Potter', 'Harry')
   Into vol_con(prim_key, last_name, first_name)
   Values(258, 'Weasley', 'Ron')
   Into vol_con(prim_key, last_name, first_name)
   Values(801, 'Granger', 'Hermione')
   Into vol_con(prim_key, last_name, first_name)
   Values(500, 'Malfoy', 'Draco')
   Select 1 from DUAL;
   
CREATE TABLE vol_evn
( prim_key integer NOT NULL,
 event_date date,
 CONSTRAINT vol_evn_pk PRIMARY KEY (prim_key)
);
Insert All
   Into vol_evn(prim_key, event_date)
   Values(1, to_date('30-mar-2023', 'dd-mon-yyyy'))
   Into vol_evn(prim_key, event_date)
   Values(2, to_date('18-apr-2023', 'dd-mon-yyyy'))
   Select 1 from DUAL;

The following code generates a report

select
    *
from
    (select
        vc.last_name
      , va.attendance_type
      , ve.event_date
    from
        vol_att va
            full outer join vol_evn ve
              on va.event_fkey = ve.prim_key
            full outer join vol_con vc
              on va.contact_fkey = vc.prim_key
    )
pivot (
    max(attendance_type) for event_date in 
        (to_date('2023-03-30', 'yyyy-mm-dd') Mar_30_2023, 
         to_date('2023-04-18', 'yyyy-mm-dd') Apr_18_2023
        )
      )
order by
    last_name;

The above code produces expected results that look like:

LAST_NAME MAR_30_2023 APR_18_2023
Granger I (null)
Malfoy (null) (null)
Potter Z Z
Weasley (null) I

I'd like to make a new report that would count the attendance (Anything that isn't null). I'd like the new report to look like:

MAR_30_2023 APR_18_2023
2 2

How can I alter my code so that it produces a report in two rows: The header of dates and the second row containing the counts? Thanks for looking at this.


Solution

  • You could take out the last_name field, and require that attendance_type is not null. Then pivot, using the count of distinct records from the vol_con table (having last_name) as the data. Because of the constraints, you can replace the full outer joins with an inner join and left outer join respectively:

    select *
    from
        (select     ve.event_date,
                    vc.prim_key
         from       vol_att va
         inner join vol_evn ve 
                 on va.event_fkey = ve.prim_key
         left join  vol_con vc
                 on va.contact_fkey = vc.prim_key
         where      va.attendance_type is not null
         )
    pivot (
         count(distinct prim_key) for event_date in (
             to_date('2023-03-30', 'yyyy-mm-dd') Mar_30_2023, 
             to_date('2023-04-18', 'yyyy-mm-dd') Apr_18_2023
         )
    );