Running Totals made easy (no Analytic SQL needed)

This subject is a cause of many headaches for developers. This example will use the Northwind demo database (which I have imported into Oracle).

Some RDBMS vendors support this functionality via analytic SQL.

Let's first show you how this is done, using an advanced database like Oracle.

select
Country, City, CompanyName, OrderTotal,
sum(OrderTotal) over (order by country, city, CompanyName, orderid) as RunningTot,
rank() over ( order by OrderTotal desc) as OverallRank,
sum(OrderTotal) over (partition by Country order by city, CompanyName, orderid) as CtryRunningTot,
sum(OrderTotal) over (partition by Country, City order by companyname, orderid) as CityRunningTot,
OrderId, SOLDBY as Agent, OrderDate, ORDERYEAR
from ordersmatrix
order by 1,2,3, orderid

So we're using 2 rather simple analytic functions in Oracle and for the Country and City within country accumulations, we're performing a window partitioning to break down the data into smaller sets. Notice that the order by clause is the same wherever it is found. Different Order By clauses can be coded in the partitions, but it can really confuse the results. Let's keep it relatively simple.

Sadly, Microsoft Sql Server (even 2008) still lags many years behind Oracle in its ability to perform analytic functions in a neat, concise and easy-to-understand manner. I ran across this article, authored by Jeff Moden, someone I have utmost respect for, especially when I look at the level of detail he has gone to.

Now if you would like to see the exact same results but made as easy as it could ever get, go and take a look at the recording of this functionality using BI Plus, a product of SeeMoreData.

The obvious advantage here is that BI Plus does not care if this data was retrieved from Oracle, Sql Server, XML, Mainframe, Access, CSV or even a tab-delimited text file, it will work the same, every time and there is hardly any work to be done. Moreover it supports group total breaks, conditional formatting, drilldown and easy integration with SOA.

Imagine that? Being able to perform this without having to understand the nuances of every RDBMS who do it in a slightly different way (if at all).

Unless, of course, you wish to follow the Masochistic way of doing things via Transact-SQL (actually, pick any procedural language that support SQL, no need to single out just 1) and for-cursor loop processing. As for me, I'm reasonably confident that I can find better ways of spending my time and avoid stress and blood pressure issues.