Hi it’s Jason again. I enjoyed writing my last post on SQL pivot so much that today I thought that I would explore another rarely used feature of this ubiquitous query language – the window clause.
As before I am using SQL Anywhere 10 but the same technique will work with several modern databases such as Oracle or to a limited extent (with 2005 anyway, it only supports the partition by clause in ranking functions) SQL Server. Let’s start with some test data:
create table orders (order_dt datetime, total int, customer_id int)
insert into orders values (‘2008-01-01’, 50, 1)
insert into orders values (‘2008-01-02’, 100, 2)
insert into orders values (‘2008-01-10’, 75, 3)
insert into orders values (‘2008-01-11’, 65, 1)
insert into orders values (‘2008-01-12’, 85, 2)
insert into orders values (‘2008-02-01’, 39, 1)
Now execute the following SQL:
select
customer_id,
order_dt,
total,
sum(total) over (partition by customer_id order by order_dt) running_total
from orders
order by
customer_id,
order_dt
You’ll see that a running total is displayed for the each customer’s orders. Examining the SQL what might look a little unusual is the over keyword and what follows it inside the parenthesis. This is the window clause and there are zero, one or two parts to it. The partition by syntax allows you to create a group within the selected data. And the order by clause rather unsurprisingly allows you to order the results. Also note how you are able to perform aggregation in a scenario when there is none. The possibilities here are endless.