ReadVitamin

COUNT ():- As you know when you use this function as aggregate function, it would just return the total number of rows present in the SELECT statement. But the same function when used with OLAP, returns the cumulative or moving count for an expression, let’s see how this is done. Let’s also use the same data set present in the initial article. Scenario is to find out, number of stores selling a particular product.

This is called grouping count and the same can be done in two ways one with taking store_no as count, partitioning by prod_code and the other ways to reverse the logic of taking prod_code as count and partition by store_no.

The following queries would give the desired result.

SELECT store_no, sale_month, actual_sale, prod_code,

COUNT (prod_code) OVER (PARTITION BY store_noROWS BETWEEN UNBOUNDED PRECEDING ANDUNBOUNDED FOLLOWING)

FROM sales;

SELECT store_no, sale_month, actual_sale, prod_code,

COUNT (store_no) OVER (PARTITION BY prod_codeROWS BETWEEN UNBOUNDED PRECEDING ANDUNBOUNDED FOLLOWING)

FROM sales;

store_no sale_month actual_sale prod_code Group Count(store_no)

10 01/01/2007 13,500 Apple 2

60 06/01/2007 11,500 Apple 2

10 05/01/2007 13,500 Apricot 2

40 04/01/2007 12,500 Apricot 2

70 07/01/2007 11,500 Banana 2

20 02/01/2007 11,000 Banana 2

50 05/01/2007 12,500 Grapes 2

80 07/01/2007 15,500 Grapes 2

50 01/01/2007 10,500 Guava 2

40 02/01/2007 11,500 Guava 2

30 03/01/2007 12,500 Mango 2

20 04/01/2007 12,500 Mango 2

30 03/01/2007 10,500 Orange 2

90 06/01/2007 15,500 Orange 2

30 02/01/2007 10,000 Strawberry 1

New Terms:-

ROWS BETWEEN: - This clause is to specify the start and end of the aggregation group.The default aggregation group is ROWS BETWEEN UNBOUNDED PRECEDING ANDUNBOUNDED FOLLOWING (take out this portion from the above query and run).

UNBOUNDED PROCEEDING: - When this is specified, the entire partition that precedes the current row is taken into the aggregation group.

UNBOUNDED FOLLOWING: - Used to define the entire partition that follows the current row.

Popularity: 26% [?]