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