How to return the total amount when using OFFSET and FETCH NEXT?

Question

I had to use OFFSET and FETCH NEXT to return a data range between my query.

Table

CREATE TABLE [dbo].[Usuario]
(
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Email] [nvarchar](max) NULL,
  [Nome] [nvarchar](max) NULL,
  [SobreNome] [nvarchar](max) NULL
);
    
INSERT INTO [dbo].[Usuario]([Email],[Nome],[SobreNome])
VALUES
(1,'[email protected]','Marconi Barroso'),
(2,'[email protected]','Magno Barroso');

Select:

SELECT COUNT(1) Total, * from Usuario
GROUP BY Id, Email, Nome, SobreNome
ORDER BY Id
OFFSET 1 ROWS
FETCH NEXT 2 ROWS ONLY

But I need to know the total amount of record because I am paying the results in a table.

See that this result returns me only 1 record when I actually have 2, how could I add a COUNT that would return to me all the records?

Sqlfiddle

Answer 1

Here are two alternatives. The first one using the COUNT function, as a window function.

SELECT COUNT(1) OVER() AS total, 
       T1.* 
  FROM Usuario T1
 ORDER BY Id
OFFSET 1 ROWS
FETCH NEXT 2 ROWS ONLY
;

The OVER() is normally used to define the "window" (or range of lines/records) over the full query result set, over which the window function (here the COUNT) will be applied.

In this case, since no partition has been specified and no filter has been applied to the User table, the COUNT function will be applied to the complete result set, i.e., it will return the total number of existing records in the User table.

The second alternative, more conventional, makes the calculation of the total separate.

SELECT  X.Total,
        T1.ID, 
        T1.Email, 
        T1.Nome, 
        T1.SobreNome
FROM Usuario T1 
CROSS JOIN ( SELECT COUNT(1) AS Total From Usuario ) X
ORDER BY T1.ID
OFFSET 1 ROWS
FETCH NEXT 2 ROWS ONLY
;

Although the result is the same in this particular case, I changed here for CROSS JOIN instead of CROSS APPLY. I think it is not justified in this particular case as CROSS APPLY is normally used when we want to establish a dependency between the two tables, or for example, apply a function to each result line. final answer just because I was playing with the fiddle and comparing the results of both versions. I’m sorry for the same.

Returning to the answer, this second, perhaps more intuitive way, carries out a Cartesian product between the result sets of T1 (Usuarios) and X, i.e., returns the result of combining each of the rows of table T1 with the rows of table X. As in this case X has only 1 record, the final result consists of the rows of the User table with an additional column that corresponds to the total of records.

This second alternative, depending on the size of the table, may be faster. But I like the first :)

Stay here the fiddle