Chapter 5 homework notes

Chapter 5 homework takes a bit to get comfortable and competent with, but with persistence and practice it's very doable.  Part of the challenging part is trying to figure out exactly what the question is asking for (yay word problems!), the figuring out how you're going to write it in TSQL..  Understanding the over clause and how partitions work can be confusing as well, so I wanted to write an example of some simple code (question 1 of last homework set) with a slight variation so that it's easier to understand.

This second part of the code is the answer to the question, can you figure out what the first one does?

SELECT custid, orderid, val,
SUM(val) OVER(PARTITION BY custid, orderid) AS custot,
SUM(val) OVER() AS grandtot
FROM sales.ordervalues;
--
SELECT custid, orderid, val,
SUM(val) OVER(PARTITION BY custid) AS custot,
SUM(val) OVER() AS grandtot
FROM sales.ordervalues

The first one helps you understand the over clause more clearly because when you run both queries at the same time you see that the second set of  code partitions ONLY by custid, while the first set partitions by custid AND orderid... which there are no duplicates of.  Notice how the 'val' and 'custot' column are the same when running the first set of code.  Hopefully this helps in understanding the OVER clause that uses PARTITION...