Select users' info with their total number of comments

Question

I have two tables:

User    : (id, username, created_at, updated_at)

Comment : (comment_id, user_id, username, created_at, updated_at)

Note: yes, I do understand Comment table has a duplicated field, 'username'. However, the table is already designed in that way and I have no permission to redesign the schema.

And this is an output format how I want to extract data from tables.

id | username | num_of_counts

And this is two different sql codes I've tried with (I've simplified the codes to show you what I'm trying to do... minor typos may exist but general ideas are here.)

-- Ver 1
SELECT u.id, u.username, COUNT(c.id)
FROM User u
LEFT JOIN Comment c ON u.id = c.id
GROUP BY u.id

-- Ver 2
SELECT u.id, u.username, c.cnt
FROM User u
LEFT JOIN (SELECT id, COUNT(*) AS cnt
           FROM Comment
           GROUP BY user_id) c
       ON u.id = c.id
GROUP BY u.id

Both codes gives me the same issue:

"Column 'username' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

After reading some articles regarding it, I've learned that there's a conflict between selecting 'username' and grouping rows by 'id'.

I'm still googling and reading similar cases but still getting the same issue (I'm not that good at sql stuff...)

What would be the best way to code sql query to get outputs in this format?

id | username | num_of_comments    
1  |    Tyler |      3    
2  |    Jane  |      5    
3  |    Jack  |      1

I would suggest you to add username, cnt to group by clause. i.e. group by u.id, u.username, c.cnt — ankur140290, Jul 11 '15 at 09:49
@GordonLinoff You were right!!!! I hit mysql instead of sql-server. Sorry It was around 2-3am and I was too tired when asking this question...Fixed. — Raccoon, Jul 11 '15 at 13:06
@Raccoon The first version is what you want, but you have to add u.username to the group by clause as all columns used in the select that are not aggregated must appear in the group by. — jpw, Jul 11 '15 at 13:12

score 3 · Answer 1 · answered Jul 11 '15 at 09:50

3

SELECT u.id, u.username, COUNT(c.id) as theCount
FROM User u
JOIN Comment c ON u.id = c.id
GROUP BY u.id,u.username

answered Jul 11 '15 at 09:50

Drew

24,851
10
43
78

score 2 · Answer 2 · answered Jul 11 '15 at 13:13

Drew has the right answer. But I want to point out that your second query can also work. It just doesn't need a group by at the outermost level:

SELECT u.id, u.username, c.cnt
FROM User u LEFT JOIN
     (SELECT id, COUNT(*) AS cnt
      FROM Comment
      GROUP BY user_id
     ) c
     ON u.id = c.id;

Under some circumstances, this can even have better performance -- for instance, if username were a really, really long string.

score 0 · Accepted Answer · answered Jul 13 '15 at 09:26

Neither has solved the issue.... :'(

SELECT
*,
(SELECT COUNT(id) FROM Comment WHERE id = id) AS Comments
FROM User
ORDER BY id DESC

This work-around has solved the issue... it's a simplified version of what i've actually coded tho. I still appreciate your answers.

Select users' info with their total number of comments

3 Answers3