How to select all users who made more than 10 submissions

Question

I have a presentation table that is very simple: userId, submissionGuid

I want to select the username (a simple internal join to get it) of all users who have more than 10 views in the table.

I would do this with inline queries and a group to count views ... but is there a better way to do this (without inline queries)?

Thanks!

Answer 1

This is the easiest way, I believe:

select userId
from submission   
group by userId
having count(submissionGuid) > 10

Answer 2

select userId, count(*)
from   submissions
having count(*) > 10
group by userId

Answer 3

SELECT 
    username 
FROM 
    usertable 
    JOIN submissions 
        ON usertable.userid = submissions.userid 
GROUP BY 
    usertable.username 
HAVING 
    Count(*) > 1

* , "" usertable "UserName"

Answer 4

, - (SQL Server):

SELECT s.userId, u.userName
FROM submission s INNER JOIN users u on u.userId = s.userId   
GROUP BY s.userId, u.username
HAVING COUNT(submissionGuid) > 10

If you do not have a HAVING offer:

SELECT u.userId, u.userName
FROM users u INNER JOIN (
    SELECT userId, COUNT(submissionGuid) AS cnt
    FROM submission
    GROUP BY userId ) sc ON sc.userId = u.userId
WHERE sc.cnt > 10

Answer 5

select userid, count (submissionGUID) as submitCount

from materials

group by userid, submitCount

has submitCount> 10