CODEWITHSUNDEEP
sqlsql-server
Julio Gallardo
Julio Gallardo

Reputation: 41

SQL subquery returning more than 1 Value

I am trying to return all the Cabin names whose number accommodation are greater than the average number of people they can accommodate for their cabin type. 

SELECT CNAME AS "CABIN NAME", NUMACCOM AS "Can Accomodate"
FROM cabinWH 
WHERE NUMACCOM > (SELECT AVG(NUMACCOM)  FROM cabinWH GROUP BY CTYPE)

Error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Upvotes: 1

Views: 108

Answers (2)

codeLover
codeLover

Reputation: 2592

You should put a where clause in your subquery to ensure that you get a single result out of it :

SELECT outerCabin.CNAME AS "CABIN NAME", outerCabin.NUMACCOM AS "Can Accomodate"
FROM cabinWH outerCabin
WHERE NUMACCOM >
(SELECT AVG(innerCabin.NUMACCOM)  FROM cabinWH innerCabin 
where outerCabin.CTYPE = innerCabin.CTYPE GROUP BY innerCabin.CTYPE)

Try this..

Upvotes: 0

Dale K
Dale K

Reputation: 27226

I think you want to consider a specific type rather than grouping by type which of course will give you multiple rows, 1 per type:

SELECT CNAME AS "CABIN NAME", NUMACCOM AS "Can Accomodate"
FROM cabinWH C1
WHERE NUMACCOM > (SELECT AVG(C2.NUMACCOM) FROM cabinWH C2 where C2.CTYPE = C1.CTYPE)

Note: you don't need to group if you want an average over the entire result set.

Upvotes: 1

Related Questions