Multiple Select against one CTE

Question

You can not use multiple select but you can use more than one CTE like this.

WITH CTEA
AS
(
SELECT 'Coulmn1' A,'Coulmn2' B
),
CETB
AS
(
SELECT 'CoulmnX' X,'CoulmnY' Y
)

SELECT * FROM CTEA, CETB

For getting count use RowNumber and CTE some think like this.

ROW_NUMBER() OVER ( ORDER BY COLUMN NAME )AS RowNumber,
Count(1) OVER() AS TotalRecordsFound

Please let me know if you need more information on this.

Sample for your reference.

With CTE AS (
         Select StudentId, S.CityId, S.GenderId
         FROM Student S
         Inner JOIN CITY C
         ON S.CityId = C.CityId
         INNER JOIN GENDER G
         ON S.GenderId = G.GenderId)
,
GENDER
AS
(
  SELECT GenderId
  FROM CTE
  GROUP BY  GenderId
  )


SELECT * FROM GENDER, CTE

It is not possible to get multiple result sets from a single CTE. You can however use a table variable to cache some of the information and use it later instead of issuing the same complex query multiple times:

declare @relevantStudent table (StudentID int);

insert into @relevantStudent
select s.StudentID from Students s
join ...
where ...

-- now issue the multiple queries

select s.GenderID, count(*)
from student s
join @relevantStudent r on r.StudentID = s.StudentID
group by s.GenderID

select s.CityID, count(*)
from student s
join @relevantStudent r on r.StudentID = s.StudentID
group by s.CityID

The trick is to store only the minimum required information in the table variable.
As with any query whether this will actually improve performance vs. issuing the queries independently depends on many things (how big the table variable data set is, how complex is the query used to populate it and how complex are the subsequent joins/subselects against the table variable, etc.).

Do a UNION ALL to do multiple SELECT and concatenate the results together into one table.

  ;WITH CTE AS(
         SELECT
         FROM Student
         INNER JOIN ...
         INNER JOIN ....)
  SELECT CityId,City,Count(studentId),NULL,NULL
         FROM CTE
         GROUP BY CityId,City
  UNION ALL
  SELECT NULL,NULL,NULL,GenderId,Gender,Count
         FROM CTE
         GROUP BY GenderId,Gender

Note: The NULL values above just allow the two results to have matching columns, so the results can be concatenated.

I know this is a very old question, but here's a solution I just used. I have a stored procedure that returns a PAGE of search results, and I also need it to return the total count matching the query parameters.

WITH results AS (...complicated foo here...)

SELECT results.*,
    CASE 
      WHEN @page=0 THEN (SELECT COUNT(*) FROM results)
      ELSE -1
    END AS totalCount 
FROM results
ORDER BY bar
OFFSET @page * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY;

With this approach, there's a small "hit" on the first results page to get the count, and for the remaining pages, I pass back "-1" to avoid the hit (I assume the number of results won't change during the user session). Even though totalCount is returned for every row of the first page of results, it's only computed once.

My CTE is doing a bunch of filtering based on stored procedure arguments, so I couldn't just move it to a view and query it twice. This approach allows avoid having to duplicate the CTE's logic just to get a count.