CODEWITHSUNDEEP
sql
rksprst
rksprst

Reputation: 6641

How to select all users who made more than 10 submissions

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

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

I would do this with embedded queries and a group by to count submissions... but is there a better way of doing it (without embedded queries)?

Thanks!

Upvotes: 3

Views: 932

Answers (5)

Gazza
Gazza

Reputation:

select userid, count(submissionGUID) as submitCount

from Submissions

group by userid, submitCount

having submitCount > 10

Upvotes: 0

EggyBach
EggyBach

Reputation: 4228

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

*Assuming that your "Users" table is call usertable and that it has a column called "UserName"

Upvotes: 1

Sklivvz
Sklivvz

Reputation: 31173

I think the correct query is this (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 don't have the HAVING clause:

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

Upvotes: 0

Blorgbeard
Blorgbeard

Reputation: 103585

This is the simplest way, I believe:

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

Upvotes: 5

cagcowboy
cagcowboy

Reputation: 30898

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

Upvotes: 1

Related Questions