57

I need help from you, this is my sql query:

select count(SID) 
from Test 
where Date = '2012-12-10' 
group by SID

this is my result:

|2|
|3|
|4|
|3|

and now I have to count the results from first query! 

Expected result: 4
Taryn
  • 242,637
  • 56
  • 362
  • 405
Butters
  • 977
  • 5
  • 14
  • 25
  • 1
    Are there any columns in your table? You want to count SID group by SID? hmm.... +1 to @bluefeet I too guess it's max count of (SID) for that date... but guess his comment is gone.. – bonCodigo Dec 12 '12 at 10:33
  • 1
    I have to count the rows! count(2,3,4,3) – Butters Dec 12 '12 at 10:34

3 Answers3

95

You can wrap your query in another SELECT:

select count(*)
from
(
  select count(SID) tot  -- add alias
  from Test 
  where Date = '2012-12-10' 
  group by SID
) src;  -- add alias

See SQL Fiddle with Demo

In order for it to work, the count(SID) need a column alias and you have to provide an alias to the subquery itself.

Taryn
  • 242,637
  • 56
  • 362
  • 405
  • 8
    Can anyone explain why you need the aliases? I tried it without them and it didn't work, but it's not obvious to me that you need them considering you never use them ('tot' and 'src' in this case) in the query. – Garrett Disco Dec 03 '15 at 23:04
  • 3
    @GarrettDisco Since you are using a subquery, each column in the subquery needs to have a name aka an alias. SQL Server also requires an alias on subqueries. – Taryn Dec 03 '15 at 23:07
9

This counts the rows of the inner query:

select count(*) from (
    select count(SID) 
    from Test 
    where Date = '2012-12-10' 
    group by SID
) t

However, in this case the effect of that is the same as this:

select count(distinct SID) from Test where Date = '2012-12-10'
dan1111
  • 6,576
  • 2
  • 18
  • 29
1

select count(*) from(select count(SID) from Test where Date = '2012-12-10' group by SID)select count(*) from(select count(SID) from Test where Date = '2012-12-10' group by SID)

should works

Daniel Mács
  • 33
  • 5