loading..
Ðóññêèé    English
17:23
go

Running totals page 2

Expansion of supporting window functions in SQLServer 2012 gives us opportunity to solve task on running totals in extremely simple manner.

As for our task, we expose the following new features:
1. Usage of sorting in OVER clause when applying aggregate functions.
2. Specifying a range which is being treated by aggregate function. In so doing the range can be limited as well as
unlimited, for example, it can extend from current row to beginning sorted row set.

Thus we can obtain running totals sorting data by date and calculating sum of values in a range from current row and up (unlimitedly) above, and we can do it with aid of a single function!

The exercise on previous page about running totals for the point 2 can now be solved as follows.

  1. SELECT point, date, out,
  2.     SUM(out) OVER (PARTITION BY point
  3.             ORDER BY date 
  4.             RANGE 
  5.             UNBOUNDED 
  6.             PRECEDING -- from the current row to beginning
  7.             ) 
  8.     FROM Outcome_o o
  9.     WHERE point = 2
  10.     ORDER BY point, date;

To obtain running totals for each point separately, we remove from above query only the condition for the point 2:

  1. SELECT point, date, out,
  2.     SUM(out) OVER (PARTITION BY point ORDER BY date RANGE UNBOUNDED PRECEDING) 
  3.     FROM Outcome_o o
  4.     ORDER BY point, date;  

The above queries have to work under PostgreSQL and Oracle. As for MySQL, window functions was supported in MySQL 8.0.

Suggested exercises: 106
Bookmark and Share
Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.