CODEWITHSUNDEEP
sql-server
el mashrafi
el mashrafi

Reputation: 182

How to display specific set of fields

Select
  tblActivity.RoomID, 
  tblRoomEquipment.EquipmentType, 
  COUNT(tblActivity.Name) AS NumberOfActivities 
from tblActivity
inner join tblRoomEquipment
on tblRoomEquipment.RoomID= tblActivity.RoomID
where tblRoomEquipment.EquipmentType = 'W' 
group by tblActivity.RoomID

In here I want to display the number of times a RoomID that has a specific equipment type in this case it is 'W' so I used the Name column from tbl Activity to determine that meaning the number of times W occurs in that Name the ID will be displayed its pretty confusing to explain.

So heres the question itself: List the number of activities that take place in rooms which have a Whiteboard in them. (5)

The error:

Msg 8120, Level 16, State 1, Line 1 Column 'tblRoomEquipment.EquipmentType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 35

Answers (1)

do_Ob
do_Ob

Reputation: 719

SELECT 
    a.RoomID, 
    re.EquipmentType, 
    COUNT(a.Name) AS NumberOfActivities 
FROM tblActivity as a
INNER JOIN tblRoomEquipment as re ON re.RoomID= a.RoomID
WHERE re.EquipmentType = 'W' 
GROUP BY a.RoomID, re.EquipmentType

Upvotes: 2

Related Questions