CODEWITHSUNDEEP
sqlsql-server
Raccoon
Raccoon

Reputation: 1427

Select users' info with their total number of comments

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

Upvotes: 1

Views: 95

Answers (3)

Raccoon
Raccoon

Reputation: 1427

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270421

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.

Upvotes: 2

Drew
Drew

Reputation: 24960

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

Upvotes: 3

Related Questions