You can mix detail elements and windowed aggregates in the same expression. For example, the following query computes for each order the percent of the current order value out of the customer total, and also the percent of the grand total.
SELECT custid, orderid, val, CAST(100.0 * val / SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5, 2)) AS pctcust, CAST(100.0 * val / SUM(val) OVER() AS NUMERIC(5, 2)) AS pcttotal FROM Sales.OrderValues;
- 5. Grouping and Windowing
- from Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012
- Publisher: Microsoft Press
- Released: December 2012
The 100.0* is simply to express as a percent 19.26 versus a ratio .1926.
Share this highlighthttp://www.safaribooksonline.com/a/training-kit-exam/61481/