TSQL – join two tables with TotalCounter and Pagination

I’m trying to join 2 tables (one to many relation) that include all the columns from first table and only the number of rows from the second one.

Use case: one Service has many Reviews.

The query looks like:

DECLARE @page int = 0;
DECLARE @pageSize int = 10;

SELECT 
    s.Id, COUNT(r.Id) AS TotalReviews 
FROM 
    dbo.Services AS s
LEFT JOIN 
    dbo.Reviews AS r ON s.Id = r.ServiceId
ORDER BY 
    s.Id DESC
    OFFSET @page ROWS FETCH NEXT @pageSize ROWS ONLY;

The error is I need to include either average or group by clause but how would that look like I can’t figure out? Any help is much appreciated

Answer

• You are using this code
• I am using SQL PARTITION BY to divide the result set into partitions and perform computation on each subset of partitioned data.

    DECLARE @page int = 0;
    DECLARE @pageSize int = 10;
    
    SELECT 
        s.Id, COUNT (r.Id) OVER (PARTITION BY r.Id) AS TotalReviews 
    FROM 
        dbo.Services AS s
    LEFT JOIN 
        dbo.Reviews AS r ON s.Id = r.ServiceId
    ORDER BY 
        s.Id DESC
        OFFSET @page ROWS FETCH NEXT @pageSize ROWS ONLY;