CODEWITHSUNDEEP
mysqlsql
Aadi
Aadi

Reputation: 162

ERROR: #1242 - Subquery returns more than 1 row in the query - MYSQL

Below is the MySQL code where a subquery is returned. And I want the subquery to return just one row.

SELECT DISTINCT w.cname
    FROM works w 
    WHERE 
    (SELECT DISTINCT w.cname, AVG(w.salary)
    FROM works w
    WHERE 1
    GROUP BY w.cname
    )

<

(SELECT DISTINCT w.cname,AVG(w.salary)
FROM works w
WHERE 1
 GROUP BY w.cname
)

MySQL error said:

1242 - Subquery returns more than 1 row

Upvotes: 1

Views: 965

Answers (1)

aaaaaa123456789
aaaaaa123456789

Reputation: 5842

First of all, the literal answer: if a subquery you use as an expression returns more than one row, modify it so that it returns at most one row.

I'm going to take a wild guess and say that you want to find workers that earn less than the average salary. The solution would be:

SELECT -- add DISTINCT if needed
  w.cname
FROM
  works w
WHERE
  w.salary < (SELECT AVG(w2.salary) FROM works w2)

Note that the subquery here (SELECT AVG(w2.salary) FROM works w2) will always return a single value, since there is no GROUP BY clause. (Also, since this instance of the works table has nothing to do with the other one, because you're getting something completely unrelated out of it, it's good practice (and required by some DBMS or another) to use a different table alias — hence w2.)

Note that, since you can reference your original table in subqueries, you can use the data in the outer query's FROM clause to filter the subquery. For instance, if you want to return every instance of people earning less than their own average salary:

SELECT DISTINCT
  w.cname, w.salary
FROM
  works w
WHERE
  w.salary < (SELECT AVG(w2.salary) FROM works w2 WHERE w2.cname = w.cname)

Note how the different table aliases (w and w2) matter here. Also, this subquery still returns only one row, because the filter by name is in the WHERE clause — it returns the average of all salary rows matching that particular name, not the average of all salary rows grouped by name. The former is one value (for one particular person), the latter is one value per person (and thus an error).

Upvotes: 3

Related Questions