SQL group by on query and subquery

February 7, 2022

I have a few tables grouped together:
Divisions > Sessions > Registrations > Registration Week Days

Each Division has several sessions, each session has several registrations, and each registration has weekday options (either M-F, MWF, T/Th).

I have this query:

SELECT d.Name, r.Weekdays, COUNT(r.weekdays) from divisions d
left join sessions s on s.DivisionID = d.ID
left join registrations r on r.SessionID = s.ID
order by r.Weekdays

This query contains all the info I need but I’m trying to group by in such a way to get weekday option count for each division, kind of like this:

Div | Weekday Option| Count
Div1| M-F           | 4
Div1| MWF           | 5
Div1| T/Th          | 3
Div2| M-F           | 7

This shouldn’t be too tough for SQL adepts, but my group by statements keep leaving out information I need and I’m not quite sure how to get the format above. Any suggestions would be great and I’m sure there are several right answers!

>Solution :

Did you try in to group "d.Name, r.Weekdays" columns?
Code is here:

SELECT d.Name, r.Weekdays, COUNT(r.weekdays) 
FROM divisions d
    left join sessions s on s.DivisionID = d.ID
    left join registrations r on r.SessionID = s.ID
group by d.Name, r.Weekdays
order by r.Weekdays