Count from a Count based on a condition in SQL server

Question

I have 4 tables in my database.

1. Students (Idno, Name, CourseId)

Sample data:

Idno    Name    CourseId
------------------------
-101123456  Vijay   101
-101123457  John    102
-101123458  Sam 101
-101123459  Arvind  102
-101123460  Smith   101

2. Courses (CourseId, CourseNo, CourseName, StreamId)

Sample data:

CourseId    CourseNo     CourseName StreamId
------------------------------------------
-101    53245   C   1
-102    53245   C++ 2

3. Streams (StreamId, StreamName)

Sample data:

StreamId    StreamName  
---------------------------
-1          Engineering
-2          Medical

4. Booking (BId, Idno, BStatus)

Sample data:

Bid     Idno        BStatus
--------------------------------
-1110   101123456   Confirmed
-1111   101123456   Confirmed
-1112   101123457   Confirmed
-1113   101123458   Confirmed
-1114   101123459   Confirmed
-1115   101123460   Confirmed
-1116   101123456   Confirmed
-1117   101123457   Confirmed
-1118   101123458   Confirmed
-1119   101123459   Confirmed
-1119   101123460   Cancelled

I have a problem generating the following output

SNo Stream        BookedOnce BookedTwice NonBooked
1    Engineering     2           3         0
2    Medical         3           1          1

Thanks

Answer 1

I think this requires a two step process. First, calculate the number of bookings by each student for a stream. Then per stream, count the number of students that have one, two or zero bookings.

Here's an example, with the first step in the inner query:

select  StreamId
,       StreamName
,       sum(case when Bookings = 1 then 1 else 0 end) as BookedOnce
,       sum(case when Bookings = 2 then 1 else 0 end) as BookedTwice
,       sum(case when Bookings = 0 then 1 else 0 end) as NoneBooked
from    (
        select  str.StreamId
        ,       str.StreamName
        ,       s.Idno
        ,       count(b.BId) as Bookings
        from    Students s
        left join
                Booking b
        on      b.Idno = s.Idno
        left join
                Courses c
        on      c.CourseId = s.CourseId
        left join
                Streams str
        on      str.StreamId = c.StreamId
        group by        
                str.StreamId
        ,       str.StreamName
        ,       s.Idno
        ) BookingsPerStudentPerStream
group by        
        StreamId
,       StreamName