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.
Using `WHERE` *outside* of the row\_number() creating query means that some rows might be filtered by that. I bet that if you comment out the line before the last (`customerTotalList>0`) you will always get 100 rows.

If you want 100, you can just use

`select top 100..........order by RowNumber asc`