SUM OVER PARTITION BY

Question

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

User · Answer

I think the query you want is this   SELECT BrandId  SUM ICount          SUM sum ICount   over    as TotalCount         100 0   SUM ICount    SUM sum Icount   over    as Percentage FROM Table  WHERE DateId    20130618 group by BrandId    This does the group by for brand   And it calculates the  Percentage    This version should produce a number between 0 and 100

User · Answer

In my opinion  I think it s important to explain the why behind the need for a GROUP BY in your SQL when summing with OVER    clause and why you are getting repeated lines of data when you are expecting one row per BrandID   Take this example  You need to aggregate the total sale price of each order line  per specific order category  between two dates  but you also need to retain individual order data in your final results   A SUM   on the SalesPrice column would not allow you to get the correct totals because it would require a GROUP BY   therefore squashing the details because you wouldn t be able to keep the individual order lines in the select statement   Many times we see a  temp table   table variable  or CTE filled with the sum of our data and grouped up so we can join to it again later to get a column of the sums we need   This can add processing time and extra lines of code   Instead  use OVER PARTITION BY     like this   SELECT   OrderLine     OrderDateTime     SalePrice     OrderCategory    SUM SalePrice  OVER PARTITION BY OrderCategory  AS SaleTotalPerCategory FROM tblSales  WHERE OrderDateTime BETWEEN  StartDate AND  EndDate   Notice we are not grouping and we have individual order lines column selected   The PARTITION BY in the last column will return us a sales price total for each row of data in each category   What the last column essentially says is  we want the sum of the sale price  SUM SalePrice   over a partition of my results and by a specified category  OVER PARTITION BY CategoryHere     If we remove the other columns from our select statement  and leave our final SUM   column  like this   SELECT   SUM SalePrice  OVER PARTITION BY OrderCategory  AS SaleTotalPerCategory FROM tblSales  WHERE OrderDateTime BETWEEN  StartDate AND  EndDate   The results will still repeat this sum for each row in our original result set   The reason is this method does not require a GROUP BY   If you don t need to retain individual line data  then simply SUM   without the use of OVER   and group up your data appropriately   Again  if you need an additional column with specific totals  you can use the OVER PARTITION BY     method described above without additional selects to join back to     The above is purely for explaining WHY he is getting repeated lines of the same number and to help understand what this clause provides   This method can be used in many ways and I highly encourage further reading from the documentation here   Over Clause

User · Answer

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

User · Answer

remove partition by and add group by clause   SELECT BrandId        SUM ICount  totalSum   FROM Table  WHERE DateId    20130618 GROUP BY BrandId

[sql] SUM OVER PARTITION BY

Examples related to sql

Examples related to sql-server

Examples related to tsql