R K Sharma R K Sharma - 27 days ago 7
SQL Question

How to get count of CTE result?

I am new to sql, I have created a CTE now I want to get the count of rows from CTE result set

DECLARE @start_date date,@end_date DATE ;

select @start_date= min(ETA) from [dbo].[testTable]
select @end_date=max(ETA) from [dbo].[testTable];

;WITH AllDays
AS (
SELECT @start_date AS [Date]
--, 1 AS [level]
UNION ALL
SELECT DATEADD(DAY, 1, [Date])
--, [level] + 1
FROM AllDays
WHERE [Date] < @end_date )
--Insert into #tempETA (CallETA)
SELECT [Date]--, [level]
FROM AllDays OPTION (MAXRECURSION 0)

select count(a.Date),a.Date from AllDays a


I am getting error here:

(1048 row(s) affected)
Msg 208, Level 16, State 1, Line 20
Invalid object name 'AllDays'.

Answer

A CTE can hold temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE. You cannot have another SELECT outside the scope of CTE

Use @@ROWCOUNT to get the count of CTE. Considering you want the CTE result and its count.

SELECT [Date]--, [level]
FROM   AllDays  OPTION (MAXRECURSION 0)

select @@ROWCOUNT

If you want to count to be part of your result then use COUNT() OVER()

SELECT [Date],count(1)over() as Total_count
FROM   AllDays  OPTION (MAXRECURSION 0)
Comments