Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, etc

Question

I have SQL query below where I am attempting to average a set of integers over the last 5 minutes. But I am getting the error that the subquery is returning more than one value, which is what I want it to do, I am just unaware of how to get the average for the top 300 values like I am wanting.

SELECT  AVG(value) AS AVERAGE,
        id_num
FROM    table_name 
AS C
WHERE   C.time=(SELECT TOP 300 time FROM table_name)
GROUP BY id_num

Answer 1

Rather than using the = operator, use the IN operator

SELECT AVG(value) AS AVERAGE, id_num 
FROM table_name AS C WHERE C.time IN (SELECT TOP 300 time FROM table_name ORDER BY TIME) 
GROUP BY id_num

edit: added order by as suggested

Answer 2

Hard to say without knowing what time in your table represents, but if you want the last 5 minutes, maybe something like this would work better:

Select ID_num, avg(value) as [average]
from table_name
group by ID_num
where time >= dateadd(minute, -5, getdate())

Edit: to get the last 5 minutes from the table, not just the last 5 minutes ever. The variable @lasttime is unnecessary - you can just put the whole max in the dateadd - but I think it is easier to follow this way.

declare @lastTime datetime = (select max(time) from table_name)

Select ID_num, avg(value) as [average]
from table_name
group by ID_num
where time >= dateadd(minute, -5, @lastTime)