Database Reference
In-Depth Information
Figure 11.3
A Simple SUM
Function.
Once again using the SUM function, let's examine total sales by country
and restrict to two continents, namely North America and Europe, as
shown in Figure 11.4.
COLUMN COUNTRY FORMAT A16
SELECT CY.NAME AS COUNTRY, SUM(S.SALE_PRICE) AS SALES
FROM CONTINENT CT, COUNTRY CY, SALES S
WHERE CT.NAME IN ('North America', 'Europe')
AND CT.CONTINENT_ID = S.CONTINENT_ID
AND CY.COUNTRY_ID = S.COUNTRY_ID
GROUP BY CY.NAME;
11.2.2.1
The OVER Clause
Now we get to the analytic part. The OVER clause in the following query
forces a cumulative sum on the SALES grouped result column, resulting in
a total sales number for each continent plus a cumulative sales number for
all rows returned so far, for every row returned. The result is shown in Fig-
ure 11.5. Neat, huh?
COLUMN CUMULATIVE FORMAT $999,990.00
SELECT COUNTRY, SALES
, SUM(SALES) OVER (ORDER BY COUNTRY) AS CUMULATIVE
FROM (
SELECT CY.NAME AS COUNTRY, SUM(S.SALE_PRICE) AS SALES
FROM CONTINENT CT, COUNTRY CY, SALES S
WHERE CT.NAME IN ('North America', 'Europe')
AND CT.CONTINENT_ID = S.CONTINENT_ID
