CODEWITHSUNDEEP
sqlmysql
gsueagle2008
gsueagle2008

Reputation: 4663

MySQL subquery returns more than one row

I am executing this query: 

SELECT
    voterfile_county.Name,
    voterfile_precienct.PREC_ID,
    voterfile_precienct.Name,
    COUNT((SELECT voterfile_voter.ID
FROM voterfile_voter
JOIN voterfile_household
WHERE voterfile_voter.House_ID = voterfile_household.ID
AND voterfile_household.Precnum = voterfile_precienct.PREC_ID)) AS Voters
FROM voterfile_precienct JOIN voterfile_county
WHERE voterfile_precienct.County_ID = voterfile_County.ID;

I am trying to make it return something like this:

County_Name   Prec_ID   Prec_Name   Voters(Count of # of voters in that precienct)

However, I am getting the error:

#1242 - Subquery returns more than 1 row.

I have tried placing the COUNT statement in the subquery but I get an invalid syntax error.

Upvotes: 18

Views: 99834

Answers (4)

Arunjith
Arunjith

Reputation: 131

See the below example and modify your query accordingly.

select COUNT(ResultTPLAlias.id) from 
(select id from Table_name where .... ) ResultTPLAlias;

Upvotes: 3

Sergey
Sergey

Reputation: 11

If you get error:error no 1242 Subquery returns more than one row, try to put ANY before your subquery. Eg:

This query return error:

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

This is good query:

SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);

Upvotes: 63

Andomar
Andomar

Reputation: 238058

You can try it without the subquery, with a simple group by:

SELECT voterfile_county.Name, 
  voterfile_precienct.PREC_ID, 
  voterfile_precienct.Name, 
  count(voterfile_voter.ID)
FROM voterfile_county
JOIN voterfile_precienct 
  ON voterfile_precienct.County_ID = voterfile_County.ID
JOIN voterfile_household 
  ON voterfile_household.Precnum = voterfile_precienct.PREC_ID
JOIN voterfile_voter 
  ON voterfile_voter.House_ID = voterfile_household.ID 
GROUP BY voterfile_county.Name, 
  voterfile_precienct.PREC_ID, 
  voterfile_precienct.Name

When you use GROUP BY, any column that you are not grouping on must have an aggregate clause (f.e. SUM or COUNT.) So in this case you have to group on county name, precienct.id and precient.name.

Upvotes: 13

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

Try this

SELECT
voterfile_county.Name, voterfile_precienct.PREC_ID, 
voterfile_precienct.Name,
    (SELECT COUNT(voterfile_voter.ID) 
    FROM voterfile_voter JOIN voterfile_household
    WHERE voterfile_voter.House_ID = voterfile_household.ID
      AND voterfile_household.Precnum = voterfile_precienct.PREC_ID) as Voters
FROM voterfile_precienct JOIN voterfile_county 
ON voterfile_precienct.County_ID = voterfile_County.ID

Upvotes: 3

Related Questions