Can I use IN to a SELECT statement?

1

Hello

Can I say
WHERE [Col1]
IN
(SELECT [Col1] FROM Table1
WHERE (SELECT AVERAGE([Col3]) FROM Table1 GROUP BY [Col1]) > 100)?

Thanks!

2

Syntactically and logically you can do what you describe (although if you are using SQL Server, the function is AVG, not AVERAGE). But, there are better ways to achieve the same result - for example

;WITH cte AS
(
    SELECT Col1,
           AVG(Col3) OVER (PARTITION BY Col1) AS AvgCol3
    FROM Table1
)
SELECT col1
FROM cte
WHERE AvgCol3 > 100;
3

Thanks, can you tell me please why the below does not work?

SELECT ORDERID
FROM ORDERDETAILS
WHERE (SELECT AVG(QUANTITY) FROM ORDERS GROUP BY ORDERID) > 5

From here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

4

So what doesn't work - do you get an error or something else?

5

why not just this

SELECT o.OrderID, AVG(o.QUANTITY)
FROM ORDERS o
join OrderDetails d
on o.orderID = d.OrderID
group by o.OrderID
having avg(o.quantity) > 5

1 Like
6

Yes, it says: Error 1: could not prepare statement (1 misuse of aggregate: AVG())

7

Thanks but I want to return only the o.OrderID and not the AVG(o.QUANTITY). Is that possible?

8

Try removing the AVG in the select:

SELECT o.OrderID
FROM ORDERS o
join OrderDetails d
on o.orderID = d.OrderID
group by o.OrderID
having avg(o.quantity) > 5
1 Like
9

That does not look like a SQL Server error - is this a different product?

10

Did you even try his solution? Just remove the AVG in the select list and see what you get...