• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • New OLAP Aggregate Functions In DB2 For i, Part 2

    July 19, 2016 Michael Sansoterra

    In my last article, I promised to tell you about four new OLAP aggregate functions: FIRST_VALUE, LAST_VALUE, nTH_VALUE and RATIO_TO_REPORT. These valuable functions can reference data from other rows in a query result set relative to the current row. This tip covers the fourth function in the list, RATIO_TO_REPORT.

    The RATIO_TO_REPORT Function

    The new RATIO_TO_REPORT function is heaven sent, as it makes it easy for developers to avoid writing an UGLY arithmetic expression to do a simple calculation common in business. RATIO_TO_REPORT simply divides a numeric column or expression value in the current row by a SUM aggregate of the same expression evaluated over the OLAP window. Quite often this result is multiplied by 100 to obtain a percentage.

    For example, consider the following query that aggregates the extended sale price by SalesOrderId and CustomerId (in this data set, the customer id has a one-to-one relationship with the sales order ID):

    SELECT sod.SalesOrderId,CustomerId,
           SUM(OrderQty*UnitPrice) AS ExtPrice
      FROM SalesOrderDetail sod
      JOIN SalesOrderHeader soh ON soh.SalesOrderId=sod.SalesOrderId
                               AND soh.OrderDate BETWEEN 
                                   '2005-01-01' AND '2005-12-31'
    GROUP BY sod.SalesOrderId,CustomerId
    

    It’s great that it’s easy to aggregate the extended sale price for an entire order. But what if management, while reviewing these orders, wants to know what the percentage of the current order’s value is with respect to all of the customer’s orders, or with respect to the overall sales?

    The RATIO_TO_REPORT function makes this task trivial:

    WITH SalesOrderSummary AS (
    SELECT sod.SalesOrderId,CustomerId,
           SUM(OrderQty*UnitPrice) AS ExtPrice
      FROM SalesOrderDetail sod
      JOIN SalesOrderHeader soh 
           ON soh.SalesOrderId=sod.SalesOrderId
          AND soh.OrderDate BETWEEN '2005-01-01' AND '2005-12-31'
    GROUP BY sod.SalesOrderId,CustomerId
    )
    SELECT sos.*,
           SUM(ExtPrice) 
               OVER(PARTITION BY CustomerId) AS Customer_Sales,
           SUM(ExtPrice) 
               OVER(PARTITION BY 1) AS Overall_Sales,
           DEC(RATIO_TO_REPORT(ExtPrice) 
               OVER(PARTITION BY CustomerId)*100,7,4)
               AS Percent_Sales_Within_Customer,
           DEC(RATIO_TO_REPORT(ExtPrice) 
               OVER(PARTITION BY 1)*100,7,4)
               AS Percent_Sales_Overall
      FROM SalesOrderSummary sos
    

    The abridged results look like this:

    Order

    Customer

    Ext Price

    Customer Sales

    Total Sales

    % Cust

    % All

    44496

    29892

    29797.16

    39980.96

    11336135.38

    74.53

    0.26

    44099

    29897

    12499.45

    17702.08

    11336135.38

    70.61

    0.11

    44760

    29897

    5202.64

    17702.08

    11336135.38

    29.39

    0.05

    43664

    29898

    24432.61

    82173.67

    11336135.38

    29.73

    0.22

    44285

    29898

    57741.06

    82173.67

    11336135.38

    70.27

    0.51

    43901

    29901

    22717.60

    63815.20

    11336135.38

    35.60

    0.20

    44551

    29901

    41097.60

    63815.20

    11336135.38

    64.40

    0.36

    43893

    29905

    419.46

    419.46

    11336135.38

    100.00

    0.00

    Within the abridged results, customer 29897 (rows two and three) has two orders totaling $17702.08. The first RATIO_TO_REPORT function in the query is used to calculate a ratio of the current order value to the entirety of the customer’s orders:

    RATIO_TO_REPORT(ExtPrice) OVER(PARTITION BY CustomerId)
    

    The second instance calculates the current row’s order amount (ExtPrice) ratio against the entirety of all orders in the result set:

    -- The current ExtPrice is the numerator for each row
    -- and 11336135.38 is the denominator for all rows
    RATIO_TO_REPORT(ExtPrice) OVER(PARTITION BY 1)
    

    The “Customer Sales” and “Total Sales” columns were included just to demonstrate the calculation is done correctly. Basically, DB2 formulates the calculation as the current row extended price/SUM (extended price) where the SUM aggregate is only taken over the requested OLAP window, as delineated by the PARTITION BY.

    Thanks to the new OLAP functionality, it’s easy for management to look at an order’s value and see how it relates to the customer’s overall sales and to the total sales.

    Hop On The Analytics Train

    Once again, the DB2 for i OLAP functions have proven their worth by mining valuable information from OLAP windows within a query result. As a bonus, these functions are incredibly easy to use and can immediately offer management better insight into company data. Finally, many of these OLAP functions appear in other database engines including SQL Server 2012 and later, Oracle, DB2 LUW, etc.

    Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

    RELATED STORIES

    New OLAP Aggregate Functions In DB2 For i, Part 1

    OLAP Aggregation Specification In DB2 For i 7.3

    LAG And LEAD Functions In DB2 for i 7.3

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    System i Developer:  RPG & DB2 Summit - October 4-6 2016 in Chicago. Register now!
    COMMON:  Open Source and Systems Management at the COMMON Forum. August 24-25 in Chicago.
    LaserVault:  Webinar → Simplify IBM i backup and recovery. July 27, 2pm EST. Enter to win a drone!

    New OLAP Aggregate Functions In DB2 for i, Part 1 Blue Stack Deadline Looms for JD Edwards Shops

    Leave a Reply Cancel reply

Volume 16, Number 16 -- July 19, 2016
THIS ISSUE SPONSORED BY:

WorksRight Software
T.L. Ashford
LaserVault

Table of Contents

  • A Second Look At SQL Descriptors
  • Side-By-Side Lists
  • New OLAP Aggregate Functions In DB2 For i, Part 2

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle