How to get the total count of records after union of CTE tables in SQL?

Question

I am applying CTE on 3 to 4 tables and combining the results using UNION.I am not storing the combined result anywhere. So now I am facing the challenge to get total number records resulted after union of these four tables.

Also I have to select limited number of rows based on certain flag set like if export to excel is set then select 25000 records else select 10000 records.

Please help me on this.

Code sample looks like below:

WITH Item_Characteristics_CTE AS
(
    SELECT 
       sequence, item_id
    FROM 
       Item_Characteristics_Log
),
Item_Required_Quantity_Log_CTE AS
(
    SELECT 
       sequence, item_id
    FROM 
       Item_Required_Quantity_Log
)
SELECT 
    c1.item_id 
FROM 
    Item_Characteristics_CTE c1
INNER JOIN 
    Item_Characteristics_CTE c2 ON c1.sequence = c2.sequence 

UNION

SELECT 
    c1.item_id AS item_id
FROM 
    Item_Required_Quantity_Log_CTE c1 
INNER JOIN        
    Item_Required_Quantity_Log_CTE c2 ON c1.sequence = c2.sequence 
WHERE 
    C2.RN = C1.RN 

Answer 1

You could wrap the query after the CTE as a subquery and count those rows.

;WITH Item_Characteristics_CTE AS (
   -- YOUR EXISTING CTE QUERIES
), Item_Required_Quantity_Log_CTE (
   -- YOUR EXISTING CTE QUERIES
)
    SELECT COUNT(*)
    FROM (
        -- YOUR EXISTING SELECT + HOWEVER YOU FILTER BY THE FLAG
    ) T

If you need the output of the CTE queries and the counts, then you could output the CTE into a temp table, then have 1 query to select from the temp table and another to count the rows.

;WITH Item_Characteristics_CTE AS (
   -- YOUR EXISTING CTE QUERIES
), Item_Required_Quantity_Log_CTE (
   -- YOUR EXISTING CTE QUERIES
)
   SELECT c1.item_id INTO #T FROM ... -- (EXISTING QUERIES)
SELECT * FROM #T
SELECT COUNT(*) FROM #T

UPDATE BASED ON FEEDBACK FOR @@ROWCOUNT AND OPTIONAL MAX ROWS

The query below takes the flag and decided how many max rows to get, then provides that max row count to an outer query of your CTE results.

DECLARE @IsExcel BIT = 1 -- Your flag whether excel or not
DECLARE @Rows INT = CASE WHEN @IsExcel = 1 THEN 25000 ELSE 10000 END
WITH Item_Characteristics_CTE AS (
    SELECT
        sequence,
        item_id
    FROM Item_Characteristics_Log
), Item_Required_Quantity_Log_CTE AS (
    SELECT
        sequence,
        item_id
    FROM Item_Required_Quantity_Log
)
    SELECT TOP (@Rows) *
    FROM (
        SELECT
            c1.item_id
        FROM Item_Characteristics_CTE c1
            INNER JOIN Item_Characteristics_CTE c2 
                ON c1.sequence = c2.sequence 
        UNION
        SELECT
            c1.item_id AS item_id
        FROM Item_Required_Quantity_Log_CTE c1
            INNER JOIN Item_Required_Quantity_Log_CTE c2
                ON c1.sequence = c2.sequence 
        WHERE C2.RN = C1.RN 
    )
DECLARE @TotalRows INT = @@ROWCOUNT -- OR "SELECT @@ROWCOUNT"

Answer 2

I am not sure what flag you mean in your above question. But to get the count, you could use one additional CTE like this:

;WITH Item_Characteristics_CTE
AS (
    SELECT sequence
        ,item_id
    FROM Item_Characteristics_Log
    )
    ,Item_Required_Quantity_Log_CTE
AS (
    SEELECT sequence
    ,item_id FROM Item_Required_Quantity_Log
    )
    ,Item_Id_Count
AS (
    SELECT c1.item_id
    FROM Item_Characteristics_CTE c1
    INNER JOIN Item_Characteristics_CTE c2 ON c1.sequence = c2.sequence

    UNION

    SELECT c1.item_id AS item_id
    FROM Item_Required_Quantity_Log_CTE c1
    INNER JOIN Item_Required_Quantity_Log_CTE c2 ON c1.sequence = c2.sequence
    WHERE C2.RN = C1.RN
    )
SELECT item_id
    ,count(item_id)
FROM Item_Id_Count