Welcome to WebmasterWorld Guest from 54.162.139.217

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)

Junior Member

5+ Year Member

joined:Nov 16, 2008
posts: 136
votes: 0


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)

Administrator

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

joined:Aug 10, 2004
posts:10887
votes: 72


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)

Junior Member

5+ Year Member

joined:Nov 16, 2008
posts: 136
votes: 0


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

Administrator

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

joined:Aug 10, 2004
posts:10887
votes: 72


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

Junior Member

5+ Year Member

joined:Nov 16, 2008
posts: 136
votes: 0


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

New User

5+ Year Member

joined:Feb 22, 2011
posts:22
votes: 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!