Adding rows automatically based on previous rows

Question

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.

What kind of SQL server are you using (MySQL, MSSQL, Oracle, PostgresQL, etc)? — Makoto
– Makoto, Commented Jul 17, 2013 at 5:43

Radu Gheorghiu · Accepted Answer · 2013-07-17 07:05:44Z

2

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

edited Jul 17, 2013 at 7:05

answered Jul 17, 2013 at 5:48

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Zec Over a year ago

No doubt someone will come up with a really hard to read single-query solution, but this one works fine. One question though, what benefit comes from using the TOP (100) PERCENT? It works just as well without it. Does it exploit some intricacy of the execution plan for better performance or something?

Radu Gheorghiu Over a year ago

@Zec I honestly have never used it, I was just researching it right now, but if the OP has used it in his attempt, I thought diverging as little as possible from his solution would make him understand my query better.

Zec Over a year ago

Yep. Fair enough. So, OP, What's the dealio?

Zec Over a year ago

Yep. Generated code explains it. Also, Radu, I've used it in ordering a view, just figured it must have some magical properties in this context. It doesn't. There is no Santa.

Damien_The_Unbeliever Over a year ago

@Barrelnc - if this is in a view, you need to be aware that, despite Management Studio inserting the TOP (100) PERCENT and ORDER BY, and pretending that views can be ordered, the actual result from querying the view may be in any order, unless there's an ORDER BY on the query that is accessing this view.

|

Collectives™ on Stack Overflow

Adding rows automatically based on previous rows

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related