How to make pagination by using offset when group by ?

Question

I work on sql server 2012 i face issue I can't using offset to make pagination
when make group by
my query as below

declare @ItemId  int = 2
declare @InventoryLocation int=1
declare @StartDate date='1900-01-01'
declare @EndDate date='01-01-2100'
declare @OptionId int=1
declare @PageNo INT=1
declare @RowCountPerPage INT=10

SELECT i.itemName,l.InventoryName, SUM(case when QTY > 0  then QTY else 0 end)  as PurchasedItem,SUM(case when QTY < 0  then -QTY else 0 end)  as ConsumItems,SUM(case when QTY > 0 then QTY else 0 end) + SUM(case when QTY < 0 then QTY else 0 end) as remaining 
FROM [dbo].[Invenroty] n with(nolock)
inner join [dbo].[InventoryLocations] l with(nolock) on l.id=n.InventoryLocID
inner join [dbo].[Items] i with(nolock) on n.itemid=i.id
inner join [dbo].[TransactionTypes] t with(nolock) on n.transactionTypeId=t.ID and InventoryLocID=case when @InventoryLocation is null then n.InventoryLocID else @InventoryLocation end
and i.id=case when @ItemId is null then n.itemid else @ItemId end 
and PostingDate between   case when @StartDate <>'1900-01-01' then @StartDate else '1900-01-01' end
and  case when @EndDate <>'2100-01-01' then @EndDate else '2100-01-01' end  


GROUP BY i.itemName,l.InventoryName

i need to add below block

  OFFSET (@PageNo - 1) * @RowCountPerPage ROWS
  FETCH NEXT @RowCountPerPage ROWS ONLY

so how to make pagination on query above using offset

Answer 1

Hi @ahmed salah ,

Please refer below and check whether it is working.

declare @ItemId  int = 2  
declare @InventoryLocation int=1  
declare @StartDate date='1900-01-01'  
declare @EndDate date='2100-01-01'  
declare @OptionId int=1  
declare @PageNo INT=1  
declare @RowCountPerPage INT=10  
       
SELECT i.itemName,l.InventoryName  
,SUM(case when QTY > 0  then QTY else 0 end) over (partition by i.itemName,l.InventoryName)  as PurchasedItem  
,SUM(case when QTY < 0  then -QTY else 0 end) over (partition by i.itemName,l.InventoryName) as ConsumItems  
,SUM(case when QTY > 0 then QTY else 0 end) over (partition by i.itemName,l.InventoryName)+ SUM(case when QTY < 0 then QTY else 0 end) over (partition by i.itemName,l.InventoryName) as remaining   
FROM [dbo].[Invenroty] n with(nolock)  
inner join [dbo].[InventoryLocations] l with(nolock) on l.id=n.InventoryLocID  
inner join [dbo].[Items] i with(nolock) on n.itemid=i.id  
inner join [dbo].[TransactionTypes] t with(nolock) on n.transactionTypeId=t.ID and InventoryLocID=case when @InventoryLocation is null then n.InventoryLocID else @InventoryLocation end  
and i.id=case when @ItemId is null then n.itemid else @ItemId end   
and PostingDate between   case when @StartDate <>'1900-01-01' then @StartDate else '1900-01-01' end  
and  case when @EndDate <>'2100-01-01' then @EndDate else '2100-01-01' end       
order By  itemName,InventoryName  
OFFSET (@PageNo - 1) * @RowCountPerPage ROWS  
FETCH NEXT @RowCountPerPage ROWS ONLY   

OR

declare @ItemId  int = 2  
declare @InventoryLocation int=1  
declare @StartDate date='1900-01-01'  
declare @EndDate date='2100-01-01'  
declare @OptionId int=1  
declare @PageNo INT=1  
declare @RowCountPerPage INT=10  
   
;with cte as (  
SELECT i.itemName,l.InventoryName, SUM(case when QTY > 0  then QTY else 0 end)  as PurchasedItem,SUM(case when QTY < 0  then -QTY else 0 end)  as ConsumItems,SUM(case when QTY > 0 then QTY else 0 end) + SUM(case when QTY < 0 then QTY else 0 end) as remaining   
FROM [dbo].[Invenroty] n with(nolock)  
inner join [dbo].[InventoryLocations] l with(nolock) on l.id=n.InventoryLocID  
inner join [dbo].[Items] i with(nolock) on n.itemid=i.id  
inner join [dbo].[TransactionTypes] t with(nolock) on n.transactionTypeId=t.ID and InventoryLocID=case when @InventoryLocation is null then n.InventoryLocID else @InventoryLocation end  
and i.id=case when @ItemId is null then n.itemid else @ItemId end   
and PostingDate between   case when @StartDate <>'1900-01-01' then @StartDate else '1900-01-01' end  
and  case when @EndDate <>'2100-01-01' then @EndDate else '2100-01-01' end       
GROUP BY i.itemName,l.InventoryName)  
select * from cte  
order By  itemName,InventoryName  
OFFSET (@PageNo - 1) * @RowCountPerPage ROWS  
FETCH NEXT @RowCountPerPage ROWS ONLY   

OR

declare @ItemId  int = 2  
declare @InventoryLocation int=1  
declare @StartDate date='1900-01-01'  
declare @EndDate date='2100-01-01'  
declare @OptionId int=1  
declare @PageNo INT=1  
declare @RowCountPerPage INT=10  
  
;with cte as (  
SELECT i.itemName,l.InventoryName,Qty  
FROM [dbo].[Invenroty] n with(nolock)  
inner join [dbo].[InventoryLocations] l with(nolock) on l.id=n.InventoryLocID  
inner join [dbo].[Items] i with(nolock) on n.itemid=i.id  
inner join [dbo].[TransactionTypes] t with(nolock) on n.transactionTypeId=t.ID and InventoryLocID=case when @InventoryLocation is null then n.InventoryLocID else @InventoryLocation end  
and i.id=case when @ItemId is null then n.itemid else @ItemId end   
and PostingDate between   case when @StartDate <>'1900-01-01' then @StartDate else '1900-01-01' end  
and  case when @EndDate <>'2100-01-01' then @EndDate else '2100-01-01' end       
order By  itemName,InventoryName  
OFFSET (@PageNo - 1) * @RowCountPerPage ROWS  
FETCH NEXT @RowCountPerPage ROWS ONLY )  
select itemName,InventoryName,SUM(case when QTY > 0  then QTY else 0 end)  as PurchasedItem,SUM(case when QTY < 0  then -QTY else 0 end)  as ConsumItems,SUM(case when QTY > 0 then QTY else 0 end) + SUM(case when QTY < 0 then QTY else 0 end) as remaining   
 from cte   
 GROUP BY itemName,InventoryName  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You need to add an ORDER BY as well. OFFSET-FETCH is part of the ORDER BY clause.