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.

If there were 6 items in the table ,this query returns 2 of them while i want this thing but together with the total number of rows as 6 as an XML element. — pcraft, Apr 16 '11 at 12:09
I am trying to be as clear .The tables are "joined". I am only concerned with the total number of rows matching the "whole" query/queries and not the number of rows that are returned . — pcraft, Apr 16 '11 at 12:15
@FreeAsInBeer No they are not the same . Total Rows: 6 ;Returned Rows: 2 .Its like Paging . — pcraft, Apr 16 '11 at 12:19

marc_s · Accepted Answer · 2011-04-16T12:42:54.777

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.

How to store the result of this to @query_result nvarchar(max) — pcraft, Apr 16 '11 at 12:39

Count total number of rows without the limit

1 Answers1