combine 2 query - sql server 2008 question

Question

I have this query:

select  count (convert(varchar(50), TmpDate, 103 )),convert(varchar(50), TmpDate, 103 ) 
from MEN
group by  TmpDate
order by TmpDate desc

and I need to count how many rows it returns

how I can combine select count (..... and query1 ? I need it in one query

thanks in advance

Answer 1

You can use SELECT @@ROWCOUNT to return the number of rows affected by the previous SQL statement.

See http://www.brettb.com/SQL_Help_Rowcount_Rows_Affected.asp

UPDATE: The simplest way to return a rowcount for a query is just to use a subquery:

SELECT COUNT(*) FROM (
    -- My sql statement
) AS ResultSet

For example:

SELECT COUNT(*) FROM (
    select  count (convert(varchar(50), TmpDate, 103 )),convert(varchar(50), TmpDate, 103 ) 
    from MEN
    group by  TmpDate
    order by TmpDate desc
) AS ResultSet

There are probably clever ways of figuring out the rowcount by looking at your query, however using a subquery like this doesn't require you to think too much about what the query that your executing is.