Selecting count that is less than a value in where statement-CodePudding

I am using SQL Server Management Studio. I've built a stored procedure.

I need to count how many of a session ID (such as the ID of 15) are in table RS and then eliminate any from the select statement that are above the SeatLimit in S (the seat limit is 30). COUNT(RS.SessionID) < S.SeatLimit How can I write this since it doesn't currently work? (See error below)

Error:

Msg 147, Level 15, State 1, Procedure spGetSession, Line 19 [Batch Start Line 7]
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

This is my stored procedure:

ALTER PROCEDURE [dbo].[spGetSession]
    @EventId int,
    @SessionTime datetime,
    @SessionType nvarchar(1),
    @SessionActive bit
AS
BEGIN
    SET NOCOUNT ON;

    SELECT *
    FROM RegistrantSessions RS, Sessions S
    WHERE COUNT(RS.SessionID) < S.SeatLimit 
      AND S.EventId = @EventId 
      AND S.SessionTime = @SessionTime 
      AND S.SessionType = @SessionType 
      AND S.Active = @SessionActive 
END

CodePudding user response：

You need to use HAVING.

ALTER PROCEDURE [dbo].[spGetSession]
        @EventId int,
        @SessionTime datetime,
        @SessionType nvarchar(1),
        @SessionActive bit
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    SELECT *
    FROM RegistrantSessions RS, Sessions S
    WHERE S.EventId = @EventId AND S.SessionTime = @SessionTime AND S.SessionType = @SessionType AND S.Active = @SessionActive 
    HAVING COUNT(RS.SessionID) < S.SeatLimit
END

CodePudding user response：

COUNT(RS.SessionID) counts all rows for which the session ID is not null, which is probably all rows. Then, you cannot use COUNT in the WHERE clause, because in WHERE we look at the rows before aggregation, not after it.

One way to join to get the rows with their session counts is a window function (COUNT OVER):

WITH rs AS
(
  SELECT
    registrantsessions.*,
    COUNT(*) OVER (PARTITION BY sessionid) as session_seats
  FROM registrantsessions
)
SELECT *
FROM rs
JOIN sessions s ON s.seatlimit >= rs.session_seats
WHERE s.eventid = @EventId 
  AND s.sessiontime = @SessionTime 
  AND s.sessiontype = @SessionType 
  AND s.active = @SessionActive
ORDER BY rs.sessionid;

In your original query you simply join all registrant sessions to all sessions, so I did the same. From the table names, however, I would think there should be a relation between registrant sessions and sessions, such as a session ID they have in common. If that is the case and you only want to join sessions with registrant sessions for the same session ID, then add this to the ON clause.