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
-
1Are 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– bonCodigo2012-12-12 10:33:42 +00:00Commented Dec 12, 2012 at 10:33
-
1I have to count the rows! count(2,3,4,3)Butters– Butters2012-12-12 10:34:38 +00:00Commented Dec 12, 2012 at 10:34
Add a comment
|
3 Answers
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
In order for it to work, the count(SID) need a column alias and you have to provide an alias to the subquery itself.
2 Comments
veryDisco
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.
Taryn
@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.
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'
3 Comments
dan1111
@SigiAnonym, I don't have SQL Server to test. But does the updated version work?
bonCodigo
@dan1111 try SQLFIDDLE ;) on the web.
Eng. Yvan Ngassa
do i need to add the colums at the end of the command and the alias ?
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