homepage Welcome to WebmasterWorld Guest from 54.204.182.118
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
problem with subquery return more than 1 row.
skoff

5+ Year Member



 
Msg#: 4418345 posted 2:25 pm on Feb 16, 2012 (gmt 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

 

phranque

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



 
Msg#: 4418345 posted 3:35 pm on Feb 17, 2012 (gmt 0)

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

skoff

5+ Year Member



 
Msg#: 4418345 posted 3:39 pm on Feb 17, 2012 (gmt 0)

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

phranque

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



 
Msg#: 4418345 posted 4:50 pm on Feb 17, 2012 (gmt 0)

why is the GROUP BY clause in the subquery?

skoff

5+ Year Member



 
Msg#: 4418345 posted 8:14 pm on Feb 17, 2012 (gmt 0)

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

arms



 
Msg#: 4418345 posted 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!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved