Menu
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 
806
The SQL PARTITION BY expression is a subclause of the OVER clause, which is used in almost all invocations of window functions like AVG() , MAX() , and RANK() . As many readers probably know, window functions operate on window frames which are sets of rows that can be different for each record in the query result.
The Window function uses the OVER() clause, and it can include the following functions: Partition By: This divides the rows or query result set into small partitions. Order By: This arranges the rows in ascending or descending order for the partition window. The default order is ascending.
Therefore, in conclusion, the PARTITION BY retrieves all the records in the table, while the GROUP BY only returns a limited number. One more thing is that GROUP BY does not allow to add columns which are not parts of GROUP BY clause in select statement. However, with PARTITION BY clause, we can add required columns.
The RANK() function is a window function that assigns a rank to each row in the partition of a result set. The rank of a row is determined by one plus the number of ranks that come before it. RANK() OVER ( PARTITION BY <expr1>[{,<expr2>...}] ORDER BY <expr1> [ASC|DESC], [{,<expr2>...}] )
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
114
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
