How can I combine the attendance numbers for both weekend days in a report-CodePudding

I'm using Oracle 18.c. In an Apex 19.1 application, I have the following Select statement for a chart.

Select
      le.prim_key
    , le.weekday
    , to_char(le.event_date, 'MON-DD-YYYY') as "Event_Date"
    , NVL(le.count_in_person, 0) as "In Person"
    , 'In_Person' series
From 
      LITURGY_EVENT le
Where
    le.prim_key is not null And
    le.weekday in ('Sat', 'Sun')
Order by
    le.event_date;

It works fine as-is and shows both weekend days separately. Now I'd like to combine the count_in_person for both weekend events (i.e., 12/4/2021 and 12/5/2021) and offer them as one count for the mixed dates (i.e., W.E. 12/5/2021). I can't figure out how to approach this. I tried using a CASE statement involving the Weekday column, but I got stuck there. It would be acceptable to use PL/SQL as a solution. Thanks for looking at this.

The data looks like:

I'd like the output to look something like:

By the way, the prim_key doesn't have to be in the Select clause, as long as it's part of the Where clause.

Here's some code to create and populate the table.

CREATE TABLE "LITURGY_EVENT" 
   (    "PRIM_KEY" NUMBER, 
    "LITURGY_DATE" DATE, 
    "WEEKDAY" VARCHAR2(1024 BYTE), 
    "COUNT_IN_PERSON" NUMBER 
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
;
SET DEFINE OFF;
Insert into LITURGY_EVENT (PRIM_KEY,LITURGY_DATE,WEEKDAY,COUNT_IN_PERSON) values (1342,to_date('2021-11-27 16:00:00','YYYY-MM-DD HH24:MI:SS'),'Sat',30);
Insert into LITURGY_EVENT (PRIM_KEY,LITURGY_DATE,WEEKDAY,COUNT_IN_PERSON) values (1241,to_date('2021-11-28 10:00:00','YYYY-MM-DD HH24:MI:SS'),'Sun',100);
Insert into LITURGY_EVENT (PRIM_KEY,LITURGY_DATE,WEEKDAY,COUNT_IN_PERSON) values (1341,to_date('2021-12-04 16:00:00','YYYY-MM-DD HH24:MI:SS'),'Sat',45);
Insert into LITURGY_EVENT (PRIM_KEY,LITURGY_DATE,WEEKDAY,COUNT_IN_PERSON) values (1343,to_date('2021-12-05 10:00:00','YYYY-MM-DD HH24:MI:SS'),'Sun',100);

CodePudding user response：

You can group using LISTAGG

select
    listagg(le.prim_key, ', ') within group(
    order by
        le.prim_key
    )                       as "prim_key",
    listagg(le.weekday, ', ') within group(
    order by
        le.prim_key
    )                       as "weekday",
    listagg(to_char(le.event_date, 'MON-DD-YYYY'), ', ') within group(
    order by
        le.prim_key
    )                       as "event_date",
    sum(le.count_in_person) as "in person",
    'In_Person'             series
from
    liturgy_event le
where
    le.prim_key is not null
    and le.weekday in ( 'Sat', 'Sun' )
order by
    le.event_date;

Dummy table

CodePudding user response：

Use the LEAD function to combine DB rows as a sub-query, then total the 2 days count_in_person on the outer. (see demo)

select 'Weekend' " "
     , to_char(coalesce(ndate,liturgy_date), 'Mon-dd-yyyy' "Event Date"
     , count_in_person   coalesce(ncount,0)  "In Person"
     , 'In Person' "Series"
  from (
         select liturgy_date  
              , count_in_person 
              , weekday
              , lead (liturgy_date) over( order by liturgy_date ) ndate
              , lead (count_in_person) over( order by liturgy_date ) ncount   
           from liturgy_event 
          where weekday in ('Sat','Sun')
       )
    where weekday = 'Sat';

NOTE: The weekday column is redundant and potentially a source of errors. It would be possible to enter 2021-12- 05 with weekday Sun. Better to extract it from the date (also in demo). If really wanted in table then define it as a generated virtual column.

create table liturgy_event 
   (  prim_key        number, 
      liturgy_date    date, 
      count_in_person number,
      weekday         varchar2(12) generated always as (to_char(liturgy_date, 'Dy')) virtual
   ) ;