CODEWITHSUNDEEP
sqlsql-servert-sqlcountcommon-table-expression
R K Sharma
R K Sharma

Reputation: 855

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'.

Upvotes: 2

Views: 17035

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93724

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)

Upvotes: 2

Mahesh.K
Mahesh.K

Reputation: 901

Check out this below code and tell.is this what you want to get.?

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, @start_date)
                   --, [level] + 1
                   FROM     [testTable]
                   WHERE    @start_date < @end_date )
                   --Insert into #tempETA (CallETA) 
         --SELECT [Date]--, [level]
         --FROM   AllDays  OPTION (MAXRECURSION 0)

         select count(a.Date) from AllDays a

Upvotes: 1

Related Questions