Welcome to WebmasterWorld Guest from 54.162.240.235

Forum Moderators: open

Message Too Old, No Replies

problem with subquery return more than 1 row.

     
2:25 pm on Feb 16, 2012 (gmt 0)

5+ Year Member



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
3:35 pm on Feb 17, 2012 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



i would expect you would get a row (the sum of) for each aggregation (each group)
3:39 pm on Feb 17, 2012 (gmt 0)

5+ Year Member



yep thats right! but it gives me the error.. dont know how to work around..
4:50 pm on Feb 17, 2012 (gmt 0)

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



why is the GROUP BY clause in the subquery?
8:14 pm on Feb 17, 2012 (gmt 0)

5+ Year Member



because i want the votes only for this date to be shown
3:45 am on Feb 19, 2012 (gmt 0)



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!
 

Featured Threads

Hot Threads This Week

Hot Threads This Month