{"id":515,"date":"2010-01-10T09:25:56","date_gmt":"2010-01-10T03:25:56","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=515"},"modified":"2016-01-07T11:31:10","modified_gmt":"2016-01-07T05:31:10","slug":"over-and-partition-by-on-sql-for-aggregation","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=515","title":{"rendered":"Over and Partition by on SQL for aggregation"},"content":{"rendered":"

The other day \u00c2\u00a0I had a requirement to create a report with total occurrence of an event by year month , running total and then running total within a year all in the same result set. \u00c2\u00a0After trying out different methods, I found the results can be easily obtained by using the DB2’s OLAP function \u00c2\u00a0OVER and PARTITION BY<\/p>\n

OVER( ) by lets you do aggregate \u00c2\u00a0to a subset of data.PARTITION BY let you split\/segregate the subset of data \u00c2\u00a0Here is a basic example of the OVER(PARTITION BY)<\/p>\n

select dept,avg(salary) over (partition by dept) from employee_salary order by dept;<\/span><\/p>\n

This will give the average salary of each department<\/p>\n

An example of the result set I was after and \u00c2\u00a0below is the sql that provided this result set :<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
YEAR<\/td>\nMONTH<\/td>\nEVENT_COUNT<\/td>\nROLLING_SUM<\/td>\nYEARLY_SUM<\/td>\n<\/tr>\n
2007<\/td>\n1<\/td>\n577<\/td>\n577<\/td>\n577<\/td>\n<\/tr>\n
2007<\/td>\n2<\/td>\n502<\/td>\n1079<\/td>\n1079<\/td>\n<\/tr>\n
2007<\/td>\n3<\/td>\n1042<\/td>\n2121<\/td>\n2121<\/td>\n<\/tr>\n
2007<\/td>\n4<\/td>\n1271<\/td>\n3392<\/td>\n3392<\/td>\n<\/tr>\n
2007<\/td>\n5<\/td>\n3693<\/td>\n7085<\/td>\n7085<\/td>\n<\/tr>\n
2007<\/td>\n6<\/td>\n1634<\/td>\n8719<\/td>\n8719<\/td>\n<\/tr>\n
2007<\/td>\n7<\/td>\n2558<\/td>\n11277<\/td>\n11277<\/td>\n<\/tr>\n
2007<\/td>\n8<\/td>\n3726<\/td>\n15003<\/td>\n15003<\/td>\n<\/tr>\n
2007<\/td>\n9<\/td>\n4267<\/td>\n19270<\/td>\n19270<\/td>\n<\/tr>\n
2007<\/td>\n10<\/td>\n5306<\/td>\n24576<\/td>\n24576<\/td>\n<\/tr>\n
2007<\/td>\n11<\/td>\n6270<\/td>\n30846<\/td>\n30846<\/td>\n<\/tr>\n
2007<\/td>\n12<\/td>\n5017<\/td>\n35863<\/td>\n35863<\/td>\n<\/tr>\n
2008<\/td>\n1<\/td>\n2627<\/td>\n38490<\/td>\n2627<\/td>\n<\/tr>\n
2008<\/td>\n2<\/td>\n2762<\/td>\n41252<\/td>\n5389<\/td>\n<\/tr>\n
2008<\/td>\n3<\/td>\n4735<\/td>\n45987<\/td>\n10124<\/td>\n<\/tr>\n
2008<\/td>\n4<\/td>\n3213<\/td>\n49200<\/td>\n13337<\/td>\n<\/tr>\n
2008<\/td>\n5<\/td>\n2306<\/td>\n51506<\/td>\n15643<\/td>\n<\/tr>\n
2008<\/td>\n6<\/td>\n2845<\/td>\n54351<\/td>\n18488<\/td>\n<\/tr>\n
2008<\/td>\n7<\/td>\n2727<\/td>\n57078<\/td>\n21215<\/td>\n<\/tr>\n
2008<\/td>\n8<\/td>\n2953<\/td>\n60031<\/td>\n24168<\/td>\n<\/tr>\n
2008<\/td>\n9<\/td>\n2744<\/td>\n62775<\/td>\n26912<\/td>\n<\/tr>\n
2008<\/td>\n10<\/td>\n2977<\/td>\n65752<\/td>\n29889<\/td>\n<\/tr>\n
2008<\/td>\n11<\/td>\n1478<\/td>\n67230<\/td>\n31367<\/td>\n<\/tr>\n
2008<\/td>\n12<\/td>\n2371<\/td>\n69601<\/td>\n33738<\/td>\n<\/tr>\n
2009<\/td>\n1<\/td>\n2904<\/td>\n72505<\/td>\n2904<\/td>\n<\/tr>\n
2009<\/td>\n2<\/td>\n2907<\/td>\n75412<\/td>\n5811<\/td>\n<\/tr>\n
2009<\/td>\n3<\/td>\n2193<\/td>\n77605<\/td>\n8004<\/td>\n<\/tr>\n
2009<\/td>\n4<\/td>\n1960<\/td>\n79565<\/td>\n9964<\/td>\n<\/tr>\n
2009<\/td>\n5<\/td>\n472<\/td>\n80037<\/td>\n10436<\/td>\n<\/tr>\n
2009<\/td>\n6<\/td>\n265<\/td>\n80302<\/td>\n10701<\/td>\n<\/tr>\n
2009<\/td>\n7<\/td>\n3518<\/td>\n83820<\/td>\n14219<\/td>\n<\/tr>\n
2009<\/td>\n8<\/td>\n541<\/td>\n84361<\/td>\n14760<\/td>\n<\/tr>\n
2009<\/td>\n9<\/td>\n38<\/td>\n84399<\/td>\n14798<\/td>\n<\/tr>\n
2009<\/td>\n11<\/td>\n1<\/td>\n84400<\/td>\n14799<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

In this sql I used \u00c2\u00a0the OVER() and then the OVER(PARTITION BY) OLAP function. \u00c2\u00a0The OVER(ORDER BY yearname,monthname) give me the running sum of event_count for the ongoing months. The
\nover (partition by yearname order by yearname,monthname) give me the running sum of event_count for ongoing month but reset itself at the beginning of every year.<\/p>\n

SELECT YEARNAME, MONTHNAME, mytab.count as EVENT_COUNT,
\nSUM(mytab.count) OVER (ORDER BY yearname,monthname) AS ROLLING_SUM,
\nSUM(mytab.count) OVER (PARTITION BY yearname ORDER BY yearname,monthname) as YEARLY_SUM
\nFROM
\n(select year(A.add_dt) as yearname,
\nmonth(A.add_dt) monthname ,
\ncount(1) count
\nfrom raju.hack_attempts A
\nwhere year(A.add_dt) in (2007, 2008,2009)
\ngroup by year(A.add_dt),
\nmonth(A.add_dt)
\n)mytab<\/span><\/p>\n


\n<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

The other day \u00c2\u00a0I had a requirement to create a report with total occurrence of an event by year month , running total and then running total within a year all in the same result set. \u00c2\u00a0After trying out different methods, I found the results can be easily obtained by using the DB2’s OLAP function […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[3],"tags":[270,943,266,267,268,269],"class_list":["post-515","post","type-post","status-publish","format-standard","hentry","category-db2","tag-aggregation","tag-db2","tag-olap-functions","tag-over","tag-partition-by","tag-running-total"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/515","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=515"}],"version-history":[{"count":20,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/515\/revisions"}],"predecessor-version":[{"id":1525,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/515\/revisions\/1525"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=515"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=515"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=515"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}