Wednesday, August 26, 2015

Cumulative Count


This is similar to the post on cumulative sum using the windowing function. This was needed because we had an old application that would only accept a certain number of dist_id's at a time. In this report, you can see how many rows you can take in to only get 100 rows at a time.

SELECT dist_id
     , COUNT(*)                                             AS ctr
     , SUM(COUNT(*)) OVER ( ORDER BY COUNT(*)
                            ROWS BETWEEN UNBOUNDED PRECEDING
                                  AND CURRENT ROW
                          )                                 AS tot_count
  FROM ods_prd.xref_store
 WHERE master_oid = 1
 GROUP BY dist_id
 ORDER BY 2 ;

1 comment:

  1. Thanks for letting me know what's helpful. Good luck with your project.

    ReplyDelete