SUM OVER PARTITION BY
14:33 25 Jul 2013

What am I missing?

This query is returning duplicate data over and over again. The count is correct for a complete total, but I am expecting one row, and yet I am getting the value repeated about 40 times. Any ideas?

SELECT BrandId
      ,SUM(ICount) OVER (PARTITION BY BrandId ) 
  FROM Table 
WHERE DateId  = 20130618

I get this?

BrandId ICount
2       421762
2       421762
2       421762
2       421762
2       421762
2       421762
2       421762
1       133346
1       133346
1       133346
1       133346
1       133346
1       133346
1       133346

What am I missing?

I cant remove the partition by as the entire query is like this:

SELECT BrandId
       ,SUM(ICount) OVER (PARTITION BY BrandId) 
       ,TotalICount= SUM(ICount) OVER ()    
        ,SUM(ICount) OVER () / SUM(ICount) OVER (PARTITION BY BrandId)  as Percentage
FROM Table 
WHERE DateId  = 20130618

Which returns this:

BrandId (No column name)    TotalICount Percentage
2       421762              32239892    76
2       421762              32239892    76
2       421762              32239892    76
2       421762              32239892    76
2       421762              32239892    76
2       421762              32239892    76

I would expect output something like this without having to use a distinct:

BrandId (No column name)    TotalICount Percentage
2       421762              32239892    76
9       1238442             32239892    26
10      1467473             32239892    21
sql sql-server t-sql