Problems with grouping and Sort

[michael.friis]

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.

mysqlSELECT 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?

[strawberry]

mi***********@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.

mysqlSELECT 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;

[michael.friis]

strawberry wrote:

mi***********@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.

mysqlSELECT 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.