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. 

Our Random Articles

More Links