Problems with grouping and Sort

Hi group
Im currently trying to make a sorta top10 list of people adding certain
items into my database.
Basicly i have a users table (in one database) and the list of
episodes (in another database)
The list of episodes has a user field which identifies the user who
added the specific episode
so i have tried constructing an SQL query that would count how many
rows each user has made in episodes, group them and order them by the
amount they made. I get an error though and was hoping you guys (and
gals) could help me fix it or maybe do it in a more effective way.

mysql> SELECT database1.db_users.username,
count(database2.episodes.user)
-> FROM database1.db_users, database2.episodes
-> WHERE database1.db_users.user_id = database2.episodes.user
-> GROUP BY database2.episodes.user
-> ORDER BY count(user) DESC;
ERROR 1111 (HY000): Invalid use of group function
mysql>

any ideas?

Re: Problems with grouping and Sort

michael.friis@gmail.com wrote:
> Hi group
> Im currently trying to make a sorta top10 list of people adding certain
> items into my database.
> Basicly i have a users table (in one database) and the list of
> episodes (in another database)
> The list of episodes has a user field which identifies the user who
> added the specific episode
> so i have tried constructing an SQL query that would count how many
> rows each user has made in episodes, group them and order them by the
> amount they made. I get an error though and was hoping you guys (and
> gals) could help me fix it or maybe do it in a more effective way.
>
> mysql> SELECT database1.db_users.username,
> count(database2.episodes.user)
> -> FROM database1.db_users, database2.episodes
> -> WHERE database1.db_users.user_id = database2.episodes.user
> -> GROUP BY database2.episodes.user
> -> ORDER BY count(user) DESC;
> ERROR 1111 (HY000): Invalid use of group function
> mysql>
>
> any ideas?
untested:

SELECT database1.db_users.username,
count(database2.episodes.user)
FROM database1.db_users, database2.episodes
WHERE database1.db_users.user_id = database2.episodes.user
GROUP BY database1.db_users.username
ORDER BY count(user) DESC;

Re: Problems with grouping and Sort

strawberry wrote:
> michael.friis@gmail.com wrote:
> > Hi group
> > Im currently trying to make a sorta top10 list of people adding certain
> > items into my database.
> > Basicly i have a users table (in one database) and the list of
> > episodes (in another database)
> > The list of episodes has a user field which identifies the user who
> > added the specific episode
> > so i have tried constructing an SQL query that would count how many
> > rows each user has made in episodes, group them and order them by the
> > amount they made. I get an error though and was hoping you guys (and
> > gals) could help me fix it or maybe do it in a more effective way.
> >
> > mysql> SELECT database1.db_users.username,
> > count(database2.episodes.user)
> > -> FROM database1.db_users, database2.episodes
> > -> WHERE database1.db_users.user_id = database2.episodes.user
> > -> GROUP BY database2.episodes.user
> > -> ORDER BY count(user) DESC;
> > ERROR 1111 (HY000): Invalid use of group function
> > mysql>
> >
> > any ideas?
> untested:
>
> SELECT database1.db_users.username,
> count(database2.episodes.user)
> FROM database1.db_users, database2.episodes
> WHERE database1.db_users.user_id = database2.episodes.user
> GROUP BY database1.db_users.username
> ORDER BY count(user) DESC;

thx but it still complains about the ERROR 1111 invalid use of the
group function.