How to summarise data using CTE

Question

I am trying to get a summary for event participants given an event_code. If the event is a parent event, the summary should be based on all the child events. If it is a single event (is not a parent and doesn't have a parent) the summary is based on the single event.

Thanks in advance!

Tables (https://i.sstatic.net/flMqjD6t.png)

WITH selectedEvent  AS (
    SELECT
        event.Event_code              AS Event_code,
        event.parent_code        AS parent_code,
        attguest.attended        AS attevent,
        cont.gender AS Gender
    FROM
        event event
        INNER JOIN EventAttendance att 
        ON event.event_code = att.event_code
        INNER JOIN EventGuestAttendance  attguest 
        ON att.Event_attendance_code = attguest.Event_attendance_code
        LEFT JOIN contact cont
        ON attguest.Contact_code=cont.Contact_code
        WHERE attguest.Contact_code is not null
        AND attguest.Attended='1' 
        --
        AND event.event_code=(Parent event or single event)
)
SELECT
    eOne.eOneID,
    eone.eOneParent
    
FROM selectedEvent eOne

I created the query above to get all event participants, but still having issues with the query

Answer 1

You want to use a hierarchical query to find all the descendents in the Event table and then JOIN to that and then use conditional aggregation to COUNT the contacts of each gender.

Something like:

SELECT COUNT(c.contact_code) AS total_contact_attendance,
       COUNT(CASE c.gender WHEN 'Female' THEN c.contact_code END) AS female,
       COUNT(CASE c.gender WHEN 'Male' THEN c.contact_code END) AS male
FROM   (
         SELECT CONNECT_BY_ROOT event_code AS root_event_code,
                event_code
         FROM   event
         START WITH event_code = 1
         CONNECT BY PRIOR event_code = parent_code
       ) e
       INNER JOIN EventAttendance a
       ON e.event_code = a.event_code
       INNER JOIN EventGuestAttendance g
       ON a.Event_attendance_code = g.Event_attendance_code
       LEFT JOIN contact c
       ON g.Contact_code = c.Contact_code
WHERE  g.Contact_code is not null
AND    g.Attended = 1
GROUP BY e.root_event_code

Note: this is untested because your data is in an image, which is a format that we cannot easily access.

Also note: You do not need a sub-query factoring clause (CTE).