Count total number of rows without the limit

Question

Currently its working but I want the xml to contain the total number of rows without the limit.

SET @query_result = (SELECT ID,Title 
        FROM
        ( 
            SELECT items.id AS "ID",items.title AS "Title" ,
ROW_NUMBER() OVER(ORDER BY date_added DESC) AS RowNum
                FROM [cars] 
                JOIN [items] ON items.id=cars.item_id
                WHERE
                rejected = 0 


    )AS MyDerivedTable
        WHERE
        MyDerivedTable.RowNum BETWEEN (@page-1)*2+1 AND (@page*2) 
        FOR XML PATH('car'),ROOT('items')
)

This returns

<items>
  <car>
    <ID>37</ID>
    <Title>Used 2004 Chevrolet Corvette Convertible</Title>
  </car>
</items>

I want

<items>
      <car>
        <ID>37</ID>
        <Title>Used 2004 Chevrolet Corvette Convertible</Title>
        <Count>6</Count>
      </car>
    </items>

While Count is not the number of rows returned but the total number of rows that matched the query .Or if my problem is too hard for anybody to understand ,I am looking for MSSQL alternative for FOUND_ROWS(); This question @SQL Count total number of rows whilst using LIMIT is trying to answer the same thing but I want a solution is MSSQL.

Answer 1

OK, I hope I understand now what you're trying to do. I believe you have to "turn around" your select and use a CTE (Common Table Expression) instead of a subselect to achieve this.

Try this:

DECLARE @queryResult VARCHAR(MAX)

;WITH MyDerivedTable AS
(
    SELECT 
       items.id,
       items.title,
       ROW_NUMBER() OVER(ORDER BY date_added DESC) AS RowNum
    FROM dbo.cars
    INNER JOIN dbo.items ON items.id = cars.item_id
    WHERE rejected = 0 
) 
SELECT
   @queryResult = 
   (SELECT
       ID, Title,
       (SELECT MAX(RowNum) FROM MyDerivedTable) AS 'Count'
    FROM
       MyDerivedTable
    WHERE
        RowNum BETWEEN (@page-1)*2+1 AND (@page*2) 
    FOR XML PATH('car'),ROOT('items')
   )

SELECT @queryResult

That should output your ID, Title and the Count (which is the max of the RowNum) for each car entry.