mysql sum 返回多行,MySQL子查询返回多行

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.

解决方案

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.