I'm trying to count the number of sessions in each room and each building. As of now I've written this query:
SELECT COUNT(BUILDINGNO)*COUNT(ROOMNO), BUILDINGNO, ROOMNO
FROM ROOM
GROUP BY BUILDINGNO
ORDER BY ROOMNO;
but I am getting an error saying it is missing a group by expression.
I think you should be aggregating by building and room. Also, you omitted a table which records attendance based on room and building. Your query should look something like this:
SELECT
r.BUILDINGNO,
r.ROOMNO,
COUNT(cs.SPEAKERID) AS cnt
FROM ROOM r
LEFT JOIN CONFERENCESESSION cs
ON r.BUILDINGNO = cs.BUILDINGNO AND
r.ROOMNO = cs.ROOMNO
GROUP BY
r.BUILDINGNO,
r.ROOMNO
ORDER BY
r.BUILDINGNO,
r.ROOMNO;
It is important to do a left join between the ROOM and CONFERENCESESSION tables because perhaps a given room have no sessions whatsoever. But we would still want to report a total of zero for that room.
This error is happening because there is a field in your SELECT clause that is not in your GROUP BY clause or being aggregated.
Also, using the conferencesession table and joining on the BUILDINGNO and ROOMNO fields will let you see the number of sessions.
Try this query:
SELECT
r.BUILDINGNO,
r.ROOMNO,
COUNT(s.SESSIONID) AS session_count
FROM room r
LEFT OUTER JOIN conferencesession s ON r.BUILDINGNO = s.BUILDINGNO AND r.ROOMNO = s.ROOMNO
GROUP BY r.BUILDINGNO, r.ROOMNO
ORDER BY r.BUILDINGNO, r.ROOMNO;
You can change the ORDER BY to order on any of these columns, even something like ORDER BY COUNT(*) will work.
Also, as Tim Biegeleisen has mentioned, using a LEFT OUTER JOIN will give you all room records that have no session. If you use an INNER JOIN, then any rooms with no sessions will not be shown.
I do not understand the logic you are going for, but you are getting this particular error because you have not specified all of your non-aggregation function fields in the GROUP BY clause. In other words, try modifying it to this:
SELECT COUNT(BUILDINGNO) * COUNT(ROOMNO), BUILDINGNO, ROOMNO
FROM ROOM
GROUP BY BUILDINGNO, ROOMNO
ORDER BY ROOMNO;
SELECT COUNT(BUILDINGNO)*COUNT(ROOMNO), BUILDINGNO, ROOMNO FROM ROOM GROUP BY BUILDINGNO,ROOMNO ORDER BY ROOMNO;