Getting Count and Rows in same query

Question

Is it possible to get the total table count and rows in same query. something like this

SELECT COUNT(1),*
FROM tbl
GROUP BY ALL

Answer 1

You can always try something like this:

SELECT
    COUNT(*) OVER (),
    (list of your other columns here)
FROM dbo.YourTableNameHere

The OVER() clause will give you a count of all rows right in your query.

Answer 2

You can use :

1) select column1,coulmn2,COUNT(*) OVER (PARTITION BY 1) as RowCnt from #Table;

2)Using the cross join method:

SELECT a.*, b.numRows
      FROM TABLE a
CROSS JOIN (SELECT COUNT(*) AS numRows
              FROM TABLE) b