**Adopt from**

https://www6.software.ibm.com/dw/education/dm/dm0608cao4/index.html

http://it.toolbox.com/blogs/db2luw/olap-sql-part-5-windowing-aggregates-8373

**On-Line Analytical Processing (OLAP)** functions provide the ability to return ranking, row numbering and existing column function information as a scalar value in a query result.

SELECT date, SUM(dollars) AS total_dollars,
**SUM(**SUM(dollars)**)** **OVER(ORDER BY date ROWS UNBOUNDED PRECEDING)** AS run_dollars,
SUM(quantity) AS total_qty,
SUM(SUM(quantity)) OVER(ORDER BY date ROWS UNBOUNDED PRECEDING) AS run_qty
FROM aroma.period a, aroma.sales b, aroma.product c
WHERE a.perkey = b.perkey
AND c.prodkey = b.prodkey
AND c.classkey = b.classkey
AND year = 2006
AND month = 'JAN'
AND prod_name = 'Aroma Roma'
GROUP BY date
ORDER BY date;

**OLAP ROW_NUMBER function**

You can even use OLAP functions for simple tasks such as providing line numbers in a result set, as in:

SELECT **ROW_NUMBER()** **OVER()** AS row_num, order_no, price
FROM aroma.orders;

**Using PARTITION BY - Calculate on Each Partition Separately**

SELECT date, SUM(dollars) AS total_dollars,
SUM(SUM(dollars)) OVER(PARTITION BY week ORDER BY date
ROWS UNBOUNDED PRECEDING) AS run_dollars,
SUM(quantity) AS total_qty,
SUM(SUM(quantity)) OVER(PARTITION BY week ORDER BY date
ROWS UNBOUNDED PRECEDING) AS run_qty
FROM aroma.period a, aroma.sales b, aroma.product c
WHERE a.perkey = b.perkey
AND c.prodkey = b.prodkey
AND c.classkey = b.classkey
AND year = 2006
AND month = 'JAN'
AND prod_name = 'Aroma Roma'
GROUP BY week, date
ORDER BY week, date;

The **DEC** function is used to remove all but two of the decimal places from each Price value

**Use Rank()**

SELECT store_name, district, SUM(dollars) AS total_sales,
**RANK() OVER(ORDER BY SUM(dollars) DESC) AS sales_rank**
FROM aroma.market a,
aroma.store b,
aroma.sales c,
aroma.period d
WHERE a.mktkey = b.mktkey
AND b.storekey = c.storekey
AND d.perkey = c.perkey
AND year = 2005
AND month = 'MAR'
AND region = 'West'
GROUP BY store_name, district;

**Using Date Arithmetic**

SELECT date - 3 MONTHS - 4 DAYS AS due_date,
date AS cur_date,
date + 3 MONTHS + 4 DAYS AS past_due
FROM aroma.period
WHERE year = 2004
AND month = 'JAN';

You can similarly adjust aspects of any TIME datatype (HOURS, MINUTES, and/or SECONDS) or any TIMESTAMP datatype (YEARS, MONTHS, DAYS, HOURS, MINUTES, and/or SECONDS). For more information about DATE, TIME, and TIMESTAMP arithmetic.

**Conditions on groups: HAVING clause**

SELECT column name(s)
FROM table name(s)
[WHERE search_condition]
[GROUP BY group_list]
[HAVING condition]
[ORDER BY order_list];

**WHERE Clause**

Works on rows of data prior to grouping.

Conditions cannot be expressed with set functions (for example, SUM or AVG), but column aliases for nonaggregate expressions can be used.

**HAVING Clause**

Works on the result set after grouping.

Conditions can be expressed with any set function or column alias.

Any set function can be referenced in a condition in the HAVING clause. A query with a HAVING clause must contain a GROUP BY clause, unless the select list contains only set functions. For example:

SELECT MIN(prodkey), MAX(classkey)
FROM aroma.product
HAVING MIN(prodkey) = 0;