problem with subquery return more than 1 row.

[skoff]

Hi! So when i do my query i get this error.. subquery return more than 1 row!

SELECT *,sum(vote) votetotal,
(SELECT sum(vote) FROM votes WHERE vote_date = '1329282000' group by clan_ID) as votetoday
FROM clans LEFT outer JOIN categories ON clans.categorie=categories.id LEFT outer JOIN votes ON clans.id = votes.clan_ID WHERE clans.active='1' GROUP BY votes.clan_ID ORDER BY votetoday,votetotal DESC

What im doing in that query is to go get all clans that are in my table clans and after that get the votes they get during the day that = 1329282000 (timestamp) an after this order them by the votes they get in that day and after by the votes they get in total for all time.

Hope everything's clear and that someone gonna be able to help me! Thanks a lot

[phranque]

i would expect you would get a row (the sum of) for each aggregation (each group)

[skoff]

yep thats right! but it gives me the error.. dont know how to work around..

[phranque]

why is the GROUP BY clause in the subquery?

[skoff]

because i want the votes only for this date to be shown

[arms]

the GROUP BY clause in the subquery is totally irrelevant and doesn't affect days but would create multiple rows in votes on a certain day were in more than 1 clan

Also the "SELECT *," at the beginning would give you a problem ub ab aggregate query!