james james - 3 years ago 203
SQL Question

How to get count number of each individual item

I have the following 2 tables named ROOM and SESSION respectively.

ROOM

BUILDINGNO | ROOMNO | ROOMCAPACITY
-----------+--------+-------------
B1 | R1 | 121
B1 | R2 | 253
B2 | R1 | 275
B2 | R2 | 126
B2 | R3 | 124
B3 | R1 | 256
B3 | R2 | 324
B4 | R1 | 431
B4 | R2 | 345
B4 | R3 | 231


SESSION

SESSIONID | BUILDINGNO | ROOMNO | SPEAKERID
----------+------------+--------+----------
SS01 | B1 | R1 | S1
SS02 | B2 | R1 | S2
SS03 | B1 | R2 | S2
SS04 | B4 | R2 | S4
SS05 | B3 | R2 | S5
SS06 | B3 | R1 | S5
SS07 | B4 | R2 | S3
SS08 | B1 | R2 | S2
SS09 | B2 | R3 | S4
SS10 | B4 | R1 | S3


I would like to be able to show the number of sessions located in each room. That would ideally show a table of some sort like this.

BUILDINGNO | ROOMNO | NUMBEROFSESSIONS
-----------+--------+-----------------
B1 | R1 | 1
B1 | R2 | 2
B2 | R1 | 1
B2 | R2 | 0
B2 | R3 | 1
B3 | R1 | 1
B3 | R2 | 1
B4 | R1 | 1
B4 | R2 | 2
B4 | R3 | 0


I know I need to use the COUNT and JOIN functions somewhere... but I'm a bit stuck to say the least. If anyone could point me in the right direction or explain this to me it would be greatly appreciated :)

SELECT BUILDINGNUMBER, ROOMNO FROM ROOM ... COUNT? ... JOIN?


Also what would allow me to show/hide the rows where the number of sessions = 0?

Answer Source

If you are only interested in the number of sessions within each room, then I believe you have enough information in the SESSION table.

SELECT BUILDINGNO, ROOMNO, COUNT(*)FROM SESSION 
GROUP BY BUILDINGNO, ROOMNO;

Here is a sql fiddle:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download