can I get count() and rows from one sql query in sql server?
aggregate, sql, sql-server, t-sql
Solution
Like this:
SELECT TOP 100 --optional
MC.Cnt, M.Column1, M.Column2
FROM
myTable M
CROSS JOIN
(SELECT COUNT(*) AS Cnt FROM myTable) MC
Edit: After downvote and COUNT/OVER answer. A comparison on 2 tables of mine
You can see a huge difference between my CROSS JOIN/simple aggregate and a COUNT/empty ORDER BY clause
SELECT COUNT(*) OVER() AS C, key1col, key2col
FROM myTable
(24717 row(s) affected)
Table 'Worktable'. Scan count 3, logical reads 49865, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'myTable'. Scan count 1, logical reads 77, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
StmtText
|--Nested Loops(Inner Join)
|--Table Spool
| |--Segment
| |--Index Scan(OBJECT:([MyDB].[dbo].[myTable].[IX_useful]))
|--Nested Loops(Inner Join, WHERE:((1)))
|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1005],0)))
| |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
| |--Table Spool
|--Table Spool
SELECT
MC.Cnt, M.key1col, M.key2col
FROM
myTable M
CROSS JOIN
(SELECT COUNT(*) AS Cnt FROM myTable) MC
(24717 row(s) affected)
Table 'myTable'. Scan count 2, logical reads 154, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
StmtText
|--Nested Loops(Inner Join)
|--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(int,[Expr1009],0)))
| |--Stream Aggregate(DEFINE:([Expr1009]=Count(*)))
| |--Index Scan(OBJECT:([MyDB].[dbo].[myTable].[IX_useful]))
|--Index Scan(OBJECT:([MyDB].[dbo].[myTable].[IX_useful] AS [M]))
I've repeated this on a table with 570k rows and here is the IO
Table 'Worktable'. Scan count 3, logical reads 1535456, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'myTable'. Scan count 1, logical reads 2929, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'myTable'. Scan count 34, logical reads 6438, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Problem
I'd like to get the total count of results and top n rows of some query - is it possible in one statement? I'd expect the results as: ``` count(..) column1 column2 125 some_value some_value 125 some_value some_value ``` Thank you in advance!