Sum then Divide in a Group by Clause in a VIEW

Question

So basically after creating a view with a case statement. I want to add a group by clause in the current view below. how do i do so? the group by statement example and case statements example are below

View with case statement which is working :

Create View v_ClassroomOccupancyJulytest
AS
Select 
ro.[RoomCode],
ro.[RoomName],
ro.[DeviceID],
ro.[Temperature],
ro.[LocalTime],
ctt.[DAY],
ctt.[DATETIME],
ctt.[DURATION],

CASE

WHEN (ro.LocalTime BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE)  
    AND ro.Occupancy = 1 THEN 'Booked And Occupied'

WHEN (ro.LocalTime NOT BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE ) or ctt.CLASSSTARTDATE IS NULL and ctt.CLASSENDDATE IS NULL
    AND ro.Occupancy = 1 THEN 'Not Booked but Occupied'

WHEN (ro.LocalTime BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE)
    AND ro.Occupancy = 0 THEN 'Booked but Not Occupied'

ELSE 'Not booked and Not Occupied' 

END AS ClassroomStatus


FROM V_RoomOccupancyJuly ro
left outer JOIN ClassTimeTable ctt
ON ro.RoomCode = ctt.ROOMID and (ro.LocalTime BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE)

I want to use group by to group the rows based on the [roomcode], [datetime] 30-minute interval and sum([occupancy])/3.

I ran my create view with the group by clause code below and it returns an error:

Cannot use an aggregate or a subquery in an expression used for the group by list of GROUP BY clause

an example of the group by

UPDATE

This is how the view is right now :

CREATE VIEW [iot].[v_test]
AS
SELECT
    ro.[RoomCode],
    ro.[RoomName],
    ro.[DeviceID],
    ro.[TpID],
    CAST(avg(ro.[Temperature]) AS decimal(10, 1)) AS [Temperature],
    CASE
        WHEN avg(cast(occupancy as decimal)) between 0.01 and 1.0 
            THEN '1'
        ELSE
            '0'
    END AS Occupancy,
    (DATEADD(HOUR, DATEPART(HOUR, ro.[LocalTime]), DATEADD( MINUTE, 30 * CAST((DATEDIFF(MINUTE, '19000101', ro.[LocalTime]) / 30) % 2 AS INT),
    CAST(CAST(ro.[LocalTime] AS DATE) AS DATETIME)))) AS [Time],
    ctt.[DAY],
    ctt.[CLASSSTARTDATE],
    ctt.[CLASSENDDATE],
    ctt.[SUBJECTCODE],
    ctt.[SEMESTERID],
    ctt.[DURATION],
    cs.ClassroomStatus
FROM
    [iot].[v_RoomOccupancy] ro
    LEFT OUTER JOIN
        [iot].[ClassTimeTable]  ctt
            ON ro.RoomCode = ctt.ROOMID
                AND (ro.LocalTime
                BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE
                    )
    CROSS APPLY
    (
        SELECT
            CASE
                WHEN (ro.LocalTime BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE)
                        AND (cast(occupancy as decimal) between 0.1 and 1.0 )
                    THEN 'Booked And Occupied'
                WHEN ((ro.LocalTime NOT BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE) OR ( ctt.CLASSSTARTDATE IS NULL AND ctt.CLASSENDDATE IS NULL))
                        AND (cast(occupancy as decimal) between 0.1 and 1.0 )
                    THEN 'Not Booked but Occupied'
                WHEN (ro.LocalTime BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE)
                        AND cast(occupancy as decimal) <= 0.0
                    THEN 'Booked but Not Occupied'
                WHEN ((ro.LocalTime NOT BETWEEN ctt.CLASSSTARTDATE AND ctt.CLASSENDDATE) OR (ctt.CLASSSTARTDATE IS NULL AND ctt.CLASSENDDATE IS NULL))
                        AND cast(occupancy as decimal) <= 0.0 
                    THEN 'Not Booked and Not Occupied'
                ELSE
                    'Null'
            END AS ClassroomStatus
    )AS cs
GROUP BY
    ro.[RoomCode],
    ro.[RoomName],
    ro.[DeviceID],
    ro.[TpID],

    (DATEADD(HOUR, DATEPART(HOUR, ro.[LocalTime]), DATEADD( MINUTE, 30 * CAST((DATEDIFF(MINUTE, '19000101', ro.[LocalTime]) / 30) % 2 AS INT),
    CAST(CAST(ro.[LocalTime] AS DATE) AS DATETIME)))),
    ctt.[DAY],
    ctt.[CLASSSTARTDATE],
    ctt.[CLASSENDDATE],
    ctt.[SUBJECTCODE],
    ctt.[SEMESTERID],
    ctt.[DURATION],
    cs.ClassroomStatus;

the problem right now is since rows are grouped based on columns, those which have 2 rows originally with the occupancy value of '1' and '0' and when combined have an average of 0.5 which sql server sees it as 0.

Here is an example:

Raw data

Data after running VIEW:

What it is supposed to be :

Answer 1

Based on the comments you can try:

CREATE VIEW v_ClassroomOccupancyJulytest
AS
    SELECT  ro.[RoomCode] ,
            ro.[RoomName] ,
            ro.[DeviceID] ,
            AVG(ro.[Temperature]) AS [Temperature] ,
            CASE WHEN ( SUM(Occupancy) / 3 ) > 0 THEN '1'
                 ELSE '0'
            END AS [1/2HrOccupancy] ,
            ctt.[DAY] ,
            ctt.[DATETIME] ,
            ctt.[DURATION] ,
            cs.ClassroomStatus
    FROM    V_RoomOccupancyJuly ro
            LEFT OUTER JOIN ClassTimeTable ctt ON ro.RoomCode = ctt.ROOMID
                                                  AND ( ro.LocalTime BETWEEN ctt.CLASSSTARTDATE
                                                              AND
                                                              ctt.CLASSENDDATE )
            CROSS APPLY ( SELECT    CASE WHEN ( ro.LocalTime BETWEEN ctt.CLASSSTARTDATE
                                                             AND
                                                              ctt.CLASSENDDATE )
                                              AND ro.Occupancy = 1
                                         THEN 'Booked And Occupied'
                                         WHEN ( ro.LocalTime NOT BETWEEN ctt.CLASSSTARTDATE
                                                             AND
                                                              ctt.CLASSENDDATE )
                                              OR ctt.CLASSSTARTDATE IS NULL
                                              AND ctt.CLASSENDDATE IS NULL
                                              AND ro.Occupancy = 1
                                         THEN 'Not Booked but Occupied'
                                         WHEN ( ro.LocalTime BETWEEN ctt.CLASSSTARTDATE
                                                             AND
                                                              ctt.CLASSENDDATE )
                                              AND ro.Occupancy = 0
                                         THEN 'Booked but Not Occupied'
                                         ELSE 'Not booked and Not Occupied'
                                    END AS ClassroomStatus
                        ) AS cs
    GROUP BY ro.[RoomCode] ,
            ro.[RoomName] ,
            ro.[DeviceID] ,
            ctt.[DAY] ,
            ctt.[DATETIME] ,
            ctt.[DURATION] ,
            cs.ClassroomStatus;