Why does adding count() to my sql query stop it from working?

Question

Why does this return 23 rows (the right amount):

select users.user_id, users.fname, users.lname, 
       stars.stars, comments.comment from users 
       LEFT JOIN stars on users.user_id = stars.userid 
       JOIN comments on users.user_id = comments.sender 
       where users.user_id = ? order by comments.time desc;

and this return 1 row?:

select users.user_id, users.fname, users.lname,
       stars.stars, count(distinct comments.id) as amount,
       comments.comment from users 
       LEFT JOIN stars on users.user_id = stars.userid 
       JOIN comments on users.user_id = comments.sender 
       where users.user_id = ? order by comments.time desc;

Cheers.

it would be best to show some sample data?

cctan
– cctan

2012-04-03 01:09:09 +00:00
Commented Apr 3, 2012 at 1:09 — cctan
– cctan, Commented Apr 3, 2012 at 1:09

Alex · Accepted Answer · 2012-04-03 01:06:24Z

3

you need to group the main data or do a sub-query for the field.

answered Apr 3, 2012 at 1:06

Alex

6,4652 gold badges27 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andy Lobel Over a year ago

@AmitBhargava well if i put group by comments.id at the end it returns the right amount of rows but the count() doesnt so i dont know

Alex Over a year ago

@AndyLobel what is it you are trying to select?

Andy Lobel Over a year ago

@Alex hey, I know this is old now but i still cant do it looool, well I can do it with the subquery but not the group by which is what i want. What would i actually tell it to group it by? Cheeeeeers

Alex Over a year ago

Reverse your query. SELECT [columns], [count] FROM comments JOIN users ON [condition] WHERE [condition] GROUP BY comments.sender - this will give your proper count of comments by users

Collectives™ on Stack Overflow

Why does adding count() to my sql query stop it from working?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related