CODEWITHSUNDEEP
sqlsql-server
Pelin
Pelin

Reputation: 457

ROW_NUMBER count returns invalid result

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.

Upvotes: 0

Views: 155

Answers (3)

Geovanny Hernandez
Geovanny Hernandez

Reputation: 237

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.

Upvotes: 0

Anson Aricatt
Anson Aricatt

Reputation: 393

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.

Upvotes: 3

George Menoutis
George Menoutis

Reputation: 7240

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

Upvotes: 0

Related Questions