Duplicate Total Row Count With Over

Question

I use "COUNT(*) OVER()" function with SQL Server to get total row count for paging. I use distinct keyword but it's get row count with duplicates rows.

Total row count is 772 normally, but the query returns 1024 because there are duplicate rows.

SELECT DISTINCT 
    ss.ProdSmartId,
    p.ProdId,
    ProdName,
    TotalRowCount = COUNT(*) OVER()
FROM 
    ProdSmartSorts ss
JOIN 
    dbo.Products p ON ss.ProdId = p.ProdId AND p.IsDeleted = 0 AND p.ProdStatus = 1
JOIN 
    Users u ON p.UserId = u.Id
LEFT JOIN 
    Pictures pic ON pic.ProdId = p.ProdId AND pic.IsCover = 1
ORDER BY 
    ss.ProdSmartId 
    OFFSET 1 ROWS FETCH NEXT 10000 ROWS ONLY

score 1 · Accepted Answer · answered Jan 03 '19 at 20:38

Your count(*) doesn't apply to the distinct values returned.

declare @table table (i int)
insert into @table
values
(1),
(1),
(1),
(1)

select distinct count(*) over() from @table

That's why they are called window functions. A window function then computes a value for each row in the window. Since you aren't limiting the rows in the window with something like a partition by or row or range clause, it's going to count all of them. You can use a sub-query though, or other functions. Depends on how you want to handle it.

Duplicate Total Row Count With Over

1 Answers1