CODEWITHSUNDEEP
sql
Barrelnc
Barrelnc

Reputation: 59

Adding rows automatically based on previous rows

I am trying to get information about room usage by classes at my school. I have used an SQL statement as following:

SELECT TOP (100) PERCENT Room,
                         Subject,
                         COUNT(Subject) AS Expr1
FROM dbo.Timetable
GROUP BY Room,
         Subject
ORDER BY t.Room

Say I had one room only at my school called M13. This SQL statement might give me:

Room    Subject    Expr1  
M13     English    7  
M13     Maths      41

As you can see, the total usage for this room is 48 classes per timetable cycle. If the total maximum per timetable cycle is 60 periods (6 periods a day x 10 days in a cycle) how can I automatically add a row at the end of each room showing the number of periods the room is not used for ex., a row after the two rows above:

Room    Subject    Expr1  
M13     English    7  
M13     Maths      41
M13     Not Used   12

Obviously, I would like to do the same for every room in the school but I have just shown 1 room here for simplicity.

I am not sure at all if this is possible or how it could be done so any help at all would be great.

Upvotes: 1

Views: 80

Answers (1)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

In case you're using SQL Server, the only solution that comes to my mind is to UNION the results of two SELECT statements, one in which you select the occupancy of the classes, and one in which you calculate the non-occupancy and union the results.

SELECT TOP (100) PERCENT Room,
    Subject,
    COUNT(Subject) AS Expr1
FROM dbo.Timetable
GROUP BY Room,
    Subject

UNION ALL

SELECT TOP (100) PERCENT Room, 
    'Not Used',
    60 - COUNT(Subject) AS Expr1
FROM dbo.Timetable
GROUP BY Room

ORDER BY t.Room

Here is a SQLFiddle with the query

Upvotes: 2

Related Questions