How do you count the rows in a SQL query which already used count, group by, and having before?

Question

For example, using the answer for this question:

How to select all users who made more than 10 submissions "How to select all users who made more than 10 submissions."

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

Let's say now I want to know many rows this sql statement outputted. How scalable is the solution for counting the rows of counting the rows?

Answer 1

Slight error in previously posted example, need an alias for a table name for the subquery:

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

I'm not sure about scalability, but this is the solution. If this isn't scaling well enough for you, you need to consider major design changes, like perhaps tracking those who submitted more than 10 submissions in a separate table that you update through applications that populate the submissions. Or many other possible solutions.

Answer 2

Nested queries:

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

Edited to incorporate mbrierst's comment about needing an alias (the "n" at the end) for the nested subquery. Oracle does not require this, but SQL Server does. Feel free to add a comment regarding usage on other database platforms.

Answer 3

In SQL Server you could do

select @@ROWCOUNT 

immediately following the query you posted.