Question: I am quite new to SQL Server and trying to improve myself. I prepared a statement for fetching customers that are cancelled (where customerCancel is true)

Normally, when I count total number of cancelled customers, the total number is 1050. 

What I want to do is to show first 100 users, but when I run the query below, I only get 38, when I increase RowNumber manually, the result is increasing but not being same with actual result. I will use this query for pagination.

My query:

```
SELECT
   COUNT(*) OVER() TotalRowCount,
   ID, customerNo, customerName, customerSurname, customerTitle, customerUnitList, customerTotalList 
FROM
   (SELECT
        ROW_NUMBER() OVER(ORDER BY m.ID) RowNumber, 
        COUNT(*) OVER() TotalRowCount,
        m.ID, m.customerNo, m.customerName, m.customerSurname, m.customerTitle,
        (SELECT COUNT(f.ID) 
         FROM Invoices f 
         WHERE f.Paid = 0 
           AND f.custumerCancel = 0 
           AND f.customerID = m.ID) AS customerUnitList,
        COALESCE((SELECT SUM(f.Total) AS InvoiceNo
                  FROM Invoices f 
                  WHERE f.Paid = 0 
                    AND f.custumerCancel = 0 
                    AND f.customerID = m.ID), 0) AS customerTotalList 
    FROM
        Customers m) flist 
WHERE
   customerTotalList > 0 
   AND RowNumber between 1 AND 100

```

I tried several way to fix it but no luck.

Answer: 
Try this query

```
SELECT * FROM   (SELECT Count(*) OVER()   TotalRowCount, 
               Row_number() 
                 OVER(ORDER BY id) RowNumber, 
               id, 
               customerno, 
               customername, 
               customersurname, 
               customertitle, 
               customerunitlist, 
               customertotallist 
        FROM   (SELECT m.id, 
                       m.customerno, 
                       m.customername, 
                       m.customersurname, 
                       m.customertitle, 
                       (SELECT Count(f.id) 
                        FROM   invoices f 
                        WHERE  f.paid = 0 
                               AND f.custumercancel = 0 
                               AND f.customerid = m.id)            AS 
                       customerUnitList, 
                       Isnull((SELECT Sum(f.total) AS InvoiceNo 
                               FROM   invoices f 
                               WHERE  f.paid = 0 
                                      AND f.custumercancel = 0 
                                      AND f.customerid = m.id), 0) AS 
                       customerTotalList 
                FROM   customers m) flist 
        WHERE  customertotallist > 0) x 
WHERE  rownumber BETWEEN 1 AND 100 

```

you are supposed to apply row number filter for paging, only after applying all your custom filters.
Which version of MS SQL Server do you have? This old approach was usable before to MS SQL Server 2012, from 2012 and up you have 0FFSET FETCH for pagination scenario.