MySQL Subquery Returns more than one row

Question

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 no 1242 Subquery returns more than one row.

i have tried placing the count statement in the subquery but i get a invalid syntax error.

Answer 1

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

Answer 2

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.

Answer 3

SELECT t1.LEAD_CAT_ID AS id, t1.LEAD_CAT_NAME AS catname, t1.LEAD_CAT_PRICE AS price,t2.LEAD_FNAME AS fname,t2.LEAD_LNAME AS lname,t2.ADDED_ON AS addedon,t2.LEAD_PCODE AS postalcode,(select count(t2.LEAD_ID) FROM cc_lead_contactdetails t2,cc_users_fields t3 where t2.Lead_Cat_ID = t3.lead_types AND t2.LEAD_PCODE = t3.lead_postcode AND t3.lead_qualified=1 AND t2.STATUS=1 AND t3.search=1) AS lcount FROM cc_lead_categories t1, cc_lead_contactdetails t2,cc_users_fields t3 WHERE t1.LEAD_CAT_ID = t2.Lead_Cat_ID AND t3.lead_types = t2.Lead_Cat_ID AND t2.LEAD_PCODE = t3.lead_postcode AND t3.lead_qualified=1 AND t2.STATUS=1 AND t3.search=1 group by t1.LEAD_CAT_ID

i am trying this for counting leads but i am facing problem as return same counting for each row. any body help me? thanks

Answer 4

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);