SUM OVER PARTITION BY

sql, sql-server, t-sql

Solution

You could have used `DISTINCT` or just remove the `PARTITION BY` portions and use `GROUP BY`:

SELECT BrandId
       ,SUM(ICount)
       ,TotalICount = SUM(ICount) OVER ()    
       ,Percentage = SUM(ICount) OVER ()*1.0 / SUM(ICount) 
FROM Table 
WHERE DateId  = 20130618
GROUP BY BrandID

Not sure why you are dividing the total by the count per BrandID, if that's a mistake and you want percent of total then reverse those bits above to:

SELECT BrandId
           ,SUM(ICount)
           ,TotalICount = SUM(ICount) OVER ()    
           ,Percentage = SUM(ICount)*1.0 / SUM(ICount) OVER () 
    FROM Table 
    WHERE DateId  = 20130618
    GROUP BY BrandID

Problem

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 ```

Original source