Use count() with a subquery inside it

Question

I have three tables, events, added_events, and regs.

events contains a list of events that a person can sign up for. Each event_id is unique. There are two types of events, registration types (a.k.a. reg-codes) and classes, differentiated by a part_no field (containing 'r' or 'c'). There is an event_day column which is null for all part_no = 'r' events, and for part_no = 'c' events contains the day that the class takes place. That event_day column is of type varchar not date because reasons.

added_events contains the events that people have added to their accounts. Each person will have exactly one part_no = 'r' reg-code and zero or more part_no = 'c' classes. Each person has a unique account_id which is used to link each entry in the added_events table back to their registration info in the regs table.

regs contains each attendee's name, address, etc. This table is not germane to this query in particular, I only mention it so you know from where the unique account_id field originates.

On to the issue at hand...

It's easy to get a list of all available reg-codes:

select e.event_id as reg_code
from events e
where e.part_no = 'c'

But I need to get a list of all available reg-codes, and within that query, I need a count of the number of classes each reg-code has signed up for, split up by day.

select  e.event_id as reg_code
       ,count(
              select *
              from added_events
              where --everyone who has the current row's reg-code and one or more part_no 'c' events on 04/25/2024
             ) as Num_att_Thu
       ,count(
              select *
              from added_events
              where --everyone who has the current row's reg-code and one or more part_no 'c' events on 04/26/2024
             ) as Num_att_Fri
       ,count(
              select *
              from added_events
              where --everyone who has the current row's reg-code and one or more part_no 'c' events on 04/27/2024
             ) as Num_att_Sat
from events e
where e.part_no = 'r'
group by e.event_id

but I have no idea how to make that happen. It doesn't seem to want let you put a "select blah blah blah" statement inside a count() like that.

If I instead start with the added_events table, I can manage a single day's count like this:

select   aer.event_id as reg_code
        ,count(aer.event_id) as total_att_thu
from added_events aer
where (aer.part_no = 'r')
  and (aer.account_id in (select aethu.account_id
                          from added_events aethu
                            left join events ethu on ethu.event_id = aethu.event_id
                          where aethu.part_no = 'c'
                            and ethu.event_day = '04/25/2024'
                         )
      )
group by aer.event_id
order by aer.event_id

There are two problems with this: 1) It doesn't necessarily get all reg-codes -- if a given reg-code has no class-attendees on a given day, it won't appear on this list; and 2) It's clearly impossible to make this approach work for more than one day.

So... Anyone got any ideas of how I can manage this?

The idea is for my output to look like this:

Reg-code  Num_att_Thu  Num_att_Fri  Num_att_Sat
A         12           27           8
B         18           52           19
C         22           65           21
D         0            12           3

etc., etc...

Please let me know if I was unclear on anything and I'll try to make it clearer... TIA!!

Answer 1

Here's the solution -- I pieced it together from comments on the original post. Thanks to everyone who commented! My biggest problem was trying to put the subquery inside the count() statements.

select   e.event_id as reg_code
        ,(select count(*)
          from added_events ae
          where ae.event_id = e.event_id
            and ae.account_id IN (select account_id
                                  from added_events ae
                                    left join events e on e.event_id = ae.event_id
                                  where ae.part_no = 'c'
                                    and e.event_day = '04/25/2024'
                                 )
         ) as num_att_thu
        ,(select count(*)
          from added_events ae
          where ae.event_id = e.event_id
            and ae.account_id IN (select account_id
                                  from added_events ae
                                    left join events e on e.event_id = ae.event_id
                                  where ae.part_no = 'c'
                                    and e.event_day = '04/26/2024'
                                 )
         ) as num_att_fri
        ,(select count(*)
          from added_events ae
          where ae.event_id = e.event_id
            and ae.account_id IN (select account_id
                                  from added_events ae
                                    left join events e on e.event_id = ae.event_id
                                  where ae.part_no = 'c'
                                    and e.event_day = '04/27/2024'
                                 )
         ) as num_att_sat
from events e
where e.part_no = 'r'
group by e.event_id
order by e.event_id

If I wanted to get really fancy, I could cast the event_day to a date type then throw in a FORMAT() statement to get the DOTW out of it and compare it to 'Thu' or 'Fri' or 'Sat' instead of hard-coding a date like I did, so it'd work for any show that has a class with a date that occurs on a Thursday or Friday or Saturday... Which I'll probably do later... But for now it's working, and that's all I needed.

Thanks again to everyone who commented!